<?php

namespace App\Http\Controllers\Admin;
use App\Model\Area;
use App\Model\Checkcategory;
use App\Model\Checkentry;
use App\Model\Checknegative;
use App\Model\Comcheck;
use App\Model\Comcheckagain;
use App\Model\Comcheckagaindetail;
use App\Model\Comcheckdetail;
use App\Model\Company;
use App\Model\Xtprovince;
use App\Model\Resident;
use App\Model\Demand;
use App\Model\Goods;
use App\Model\Tag;
use App\Model\TagRelation;
use App\Model\Stock;
use App\Model\Xtcity;
use App\Model\Xttown;
use App\Model\AdminUserMenu;
use App\Model\AdminUserMenuRelation;
use App\Model\AdminUserMenuMRelation;
use App\Model\AdminUserRole;
use App\Model\AdminUserRoleRelation;
use App\Model\AdminRoleMenu;
use App\Model\AdminRoleMenuM;
use App\Model\AdminRoleMenuRelation;
use App\Model\AdminRoleMenuMRelation;
use App\Model\StaffSumary;
use App\Model\Xtstreet;
use App\Model\MessageText;
use App\Model\Report;
use App\Model\Message;
use App\Model\Job;
use App\Model\FieldCom;
use App\Model\FieldResident;
use App\Model\Department;
use App\Model\Users as User;
use Illuminate\Http\Request;

use App\Http\Requests;
use App\Http\Controllers\Controller;
use Validator;
use Auth;
use DB;
class UserController extends Controller
{
    /**
     * @param Request $quest
     * @return \Illuminate\Contracts\View\Factory|\Illuminate\View\View
     */
    public function userlist(Request $quest)
    {
        $role=Auth::guard('admin')->user()->role;
        return view('admin/user/userlist',compact('role'));
    }
    
    public function userlist2(Request $quest)
    {
        $role=Auth::guard('admin')->user()->role;
        return view('admin/user/userlist2',compact('role'));
    }
    public function rolelist(Request $quest)
    {
        $role=Auth::guard('admin')->user()->role;
        return view('admin/user/rolelist',compact('role'));
    }
    
    public function deplist(Request $quest)
    {
        return view('admin/user/deplist');
    }
    public function joblist(Request $quest)
    {
        return view('admin/user/joblist');
    }
    public function comlist(Request $req)
    {
        return view('admin/com/comlist')->with('community',$req->input('community',''));
    }
    public function comlist_pro(Request $quest)
    {
        $tree = array();
        $area_first = array();
        $area_first = Area::where('name','北宅街道')->first()->toArray();
        $tree = $area_first;
        $where = array();
        $where[] = array('pid', $area_first['id']);
        $area = Area::where($where)->select(array('id','pid','node','name','level'))->get()->toArray();
        $tree['street'] = $area;
        return view('admin/com/comlist_pro')->with('tree',$tree);
    }
    public function daping_yinhuan(){
        $all=Company::count();
        $check=Company::whereHas('comcheck')->count();
        $yinhuan_all=Comcheckdetail::join('comcheck','comcheckdetail.checkId','=','comcheck.id')->join('company','comcheck.companyId','=','company.id')->where('comcheckdetail.status',2)->count();

        $yinhuan_1=Comcheckagaindetail::join('comcheck','comcheckagaindetail.checkId','=','comcheck.id')->join('company','comcheck.companyId','=','company.id')->count();
        $yinhuan_0=$yinhuan_all-$yinhuan_1;
        $percent=round(100*$yinhuan_1/$yinhuan_all).'%';
        $data=[
            'code'=>200,
            'msg'=>'请求成功',
            'data'=>compact('all','check','yinhuan_all','yinhuan_1','yinhuan_0','percent')
        ];
        return response()->json($data);
    }
    public function daping_demand(){
        $all=Demand::where('cid',0)->count();
        $reply=Demand::where('cid',0)->whereHas('reply')->count();
        $unreply=$all-$reply;
        $continueday=round((time()-strtotime('2023-09-21'))/86400);
        $tianqi='晴转多云';
        $tq=$this->doGet('https://restapi.amap.com/v3/weather/weatherInfo?key=0d716082063cbf0caf66dc35cd7868a2&city=370200',[],1);
        $tqindex=stripos($tq,'{',0);
        $tq=json_decode(substr($tq,$tqindex),1);
        $tianqi=$tq['lives'][0]['weather'].' '.$tq['lives'][0]['temperature'].'℃';
        $data=[
            'code'=>200,
            'msg'=>'请求成功',
            'data'=>compact('all','reply','unreply','continueday','tianqi')
        ];
        return response()->json($data);
    }
    public function doGet($url,$header=[],$ssl=0){
        $ch = curl_init();
        curl_setopt($ch, CURLOPT_URL,$url);
        // 执行后不直接打印出来
        curl_setopt($ch, CURLOPT_RETURNTRANSFER,1);
        if($header){
            curl_setopt($ch, CURLOPT_HEADER,0);
            curl_setopt($ch, CURLOPT_HTTPHEADER, $header);
            curl_setopt($ch, CURLOPT_ENCODING, 'gzip,deflate');
        }else{
            curl_setopt($ch, CURLOPT_HEADER,1);
        }
        if($ssl==1){
            curl_setopt($ch, CURLOPT_SSL_VERIFYPEER, false);
            curl_setopt($ch, CURLOPT_SSL_VERIFYHOST, false);
        }
        $output = curl_exec($ch);
        curl_close($ch);
        return $output;
    }
    public function daping_jujia(){
        $putong=Company::whereHas('resident',function($query){
            $query->where('old_type',3);
        })->count();
        $duju=Company::whereHas('resident',function($query){
            $query->where('old_type',1);
        })->count();
        $gugua=Company::whereHas('resident',function($query){
            $query->where('old_type',2);
        })->count();
        $putong_mei=Company::whereHas('resident',function($query){
            $query->where('old_type',3)->where('warm_type',1);
        })->count();
        $putong_kongqi=Company::whereHas('resident',function($query){
            $query->where('old_type',3)->where('warm_type',2);
        })->count();
        $putong_alarm=Company::whereHas('resident',function($query){
            $query->where('old_type',3)->where('alarm_type',2);
        })->count();

        $duju_mei=Company::whereHas('resident',function($query){
            $query->where('old_type',1)->where('warm_type',1);
        })->count();
        $duju_kongqi=Company::whereHas('resident',function($query){
            $query->where('old_type',1)->where('warm_type',2);
        })->count();
        $duju_alarm=Company::whereHas('resident',function($query){
            $query->where('old_type',1)->where('alarm_type',2);
        })->count();

        $gugua_mei=Company::whereHas('resident',function($query){
            $query->where('old_type',2)->where('warm_type',1);
        })->count();
        $gugua_kongqi=Company::whereHas('resident',function($query){
            $query->where('old_type',2)->where('warm_type',2);
        })->count();
        $gugua_alarm=Company::whereHas('resident',function($query){
            $query->where('old_type',2)->where('alarm_type',2);
        })->count();
        $mei_all=$putong_mei+$duju_mei+$gugua_mei;
        $kongqi_all=$putong_kongqi+$duju_kongqi+$gugua_kongqi;
        $alarm_all=$putong_alarm+$duju_alarm+$gugua_alarm;


        
        $data=[
            'code'=>200,
            'msg'=>'请求成功',
            'data'=>compact('putong','putong_mei','putong_kongqi','putong_alarm','mei_all','duju','duju_mei','duju_kongqi','duju_alarm','kongqi_all','gugua','gugua_mei','gugua_kongqi','gugua_alarm','alarm_all')
        ];
        return response()->json($data);
    }
    public function daping_tag(){
        $res1=TagRelation::join('company','tag_relation.com_id','=','company.id')->join('tags','tag_relation.tag_id','=','tags.id')->groupBy('tags.id','company.street1')->select('company.street1','tags.tag_name',DB::raw('count(tags.id) as total'))->orderBy('total','desc')->get();
        $bz=370212004;
        $in=[];
        $out=[];
        $tags=[];
        foreach($res1 as &$r){
            if(!in_array($r->tag_name,$tags)){
                if(count($tags)<5){
                    $tags[]=$r->tag_name;
                    $in[$r->tag_name]=0;
                    $out[$r->tag_name]=0;
                }else{
                    $r->tag_name='其他';
                    if(count($tags)<6){
                        $tags[]='其他';
                        $in['其他']=0;
                        $out['其他']=0;
                    }
                }
            }
            if($r->street1==$bz){
                // 本区
                $in[$r->tag_name]+=$r->total;
            }else{
                // 外区
                $out[$r->tag_name]+=$r->total;
            }
        }
        $data=[
            'code'=>200,
            'msg'=>'请求成功',
            'data'=>compact('in','out')
        ];
        return response()->json($data);
    }
    public function daping_base(){
        $qiye=Company::where('enterprise',1)->count();
        $jiuxiao=Company::where('enterprise',2)->count();
        $zhuhu=Company::where('enterprise',3)->count();
        $data=[
            'code'=>200,
            'msg'=>'请求成功',
            'data'=>compact('qiye','jiuxiao','zhuhu')
        ];
        return response()->json($data);
    }
    // 大屏 社区企业数接口
    public function daping_community(){
        // 企业数统计
        $res1=Company::with('place_5')->select(DB::raw('COUNT(id) as totalnum'),'community')->where('enterprise',1)->whereHas('place_5')->groupBy('community')->get();
        // 九小场所数统计
        $res2=Company::with('place_5')->select(DB::raw('COUNT(id) as totalnum'),'community')->where('enterprise',2)->whereHas('place_5')->groupBy('community')->get();
        // 住户数统计
        $res3=Company::with('place_5')->select(DB::raw('COUNT(id) as totalnum'),'community')->where('enterprise',3)->whereHas('place_5')->groupBy('community')->get();

        $arr=[];
        foreach($res1 as $r){
            if(!isset($arr[$r->community])){
                $arr[$r->community]=[
                    'num1'=>0,
                    'num2'=>0,
                    'num3'=>0,
                    'name'=>$r->place_5->name,
                    'yinhuan_all'=>0,
                    'yinhuan_1'=>0,
                    'yinhuan_0'=>0
                ];
            }
            $arr[$r->community]['num1']=$r->totalnum;
        }
        foreach($res2 as $r){
            if(!isset($arr[$r->community])){
                $arr[$r->community]=[
                    'num1'=>0,
                    'num2'=>0,
                    'num3'=>0,
                    'name'=>$r->place_5->name,
                    'yinhuan_all'=>0,
                    'yinhuan_1'=>0,
                    'yinhuan_0'=>0
                ];
            }
            $arr[$r->community]['num2']=$r->totalnum;
        }
        foreach($res3 as $r){
            if(!isset($arr[$r->community])){
                $arr[$r->community]=[
                    'num1'=>0,
                    'num2'=>0,
                    'num3'=>0,
                    'name'=>$r->place_5->name,
                    'yinhuan_all'=>0,
                    'yinhuan_1'=>0,
                    'yinhuan_0'=>0
                ];
            }
            $arr[$r->community]['num3']=$r->totalnum;
        }
        

        $yinhuan_all=Comcheckdetail::join('comcheck','comcheckdetail.checkId','=','comcheck.id')->join('company','comcheck.companyId','=','company.id')->where('comcheckdetail.status',2)->groupBy('company.community')->select('company.community',DB::raw('count(comcheckdetail.id) as total'))->get();
        foreach($yinhuan_all as $y){
            if(isset($arr[$y->community])){
                $arr[$y->community]['yinhuan_all']=$y->total;
            }
        }
        $yinhuan_1=Comcheckagaindetail::join('comcheck','comcheckagaindetail.checkId','=','comcheck.id')->join('company','comcheck.companyId','=','company.id')->groupBy('company.community')->select('company.community',DB::raw('count(comcheckagaindetail.id) as total'))->get();
        foreach($yinhuan_1 as $y){
            if(isset($arr[$y->community])){
                $arr[$y->community]['yinhuan_1']=$y->total;
            }
        }
        foreach($arr as $k=>$v){
            $lastname=str_replace('居民','',$v['name']);
            $lastname=str_replace('社区','',$lastname);
            $lastname=str_replace('委员会','',$lastname);
            $lastname=str_replace('居委会','',$lastname);
            $arr[$k]['name']=$lastname;
            $arr[$k]['yinhuan_0']=$v['yinhuan_all']>$v['yinhuan_1']?($v['yinhuan_all']-$v['yinhuan_1']):0;
        }


        $data=[
            'code'=>200,
            'msg'=>'请求成功',
            'data'=>$arr
        ];
        return response()->json($data);
    }
    public function winner(){
        // 煤取暖 人数
        $num1=Company::whereHas('resident',function($query){
            $query->where('warm_type',1);
        })->count();
        // 空气能取暖 人数
        $num2=Company::whereHas('resident',function($query){
            $query->where('warm_type',2);
        })->count();
        $num4=Company::count();
        $num3=intval($num4-$num1-$num2);
        $arr=[
            [
                'name'=>'煤取暖',
                'num'=>$num1,
                'percent'=>round(100*($num1/$num4)).'%'
            ],
            [
                'name'=>'空气能取暖',
                'num'=>$num2,
                'percent'=>round(100*($num2/$num4)).'%'
            ],
            [
                'name'=>'其他',
                'num'=>$num3,
                'percent'=>round(100*($num3/$num4)).'%'
            ]
        ];
        $data=[
            'code'=>200,
            'msg'=>'请求成功',
            'data'=>$arr
        ];
        return response()->json($data);
    }
    public function daping(){
        return view('admin/user/system',['title'=>'大屏展示','component'=>'daping']);
    }
    public function gettaglist(Request $req){
        $tags=Tag::get();
        $data=[
            'code'=>1,
            'msg'=>'获取成功',
            'data'=>$tags
        ];
        return response()->json($data);
    }
    // 保存标签
    public function savetag(Request $req){
        $user=Auth::guard('admin')->user();
        if(!$user){
            $user=Auth::guard('api')->user();
        }
        // 先把没有使用的 本次被删除的 标签删除
        $tags=$req->input('tags');
        if(!is_array($tags)){
            $tags=json_decode($tags,1);
        }
        $ids=[];
        foreach($tags as $tag){
            if($tag['tag_type']=='old'){
                $ids[]=$tag['tag_id'];
            }
        }
        $deltags=Tag::whereNotIn('id',$ids)->get();
        if($deltags){
            foreach($deltags as $tag){
                $isuse=TagRelation::where('tag_id',$tag->id)->first();
                if($isuse){
                    $data=[
                        'code'=>0,
                        'message'=>'标签《'.$tag->tag_name.'》正在使用，无法删除'
                    ];
                    return response()->json($data);
                }else{
                    $tag->delete();
                }
            }
        }
        // 遍历本次标签 该改的改 该增的增
        foreach($tags as $t){
            if($t['tag_type']=='old'){
                $tag=Tag::where('id',$t['tag_id'])->first();
                if($tag->tag_name!=$t['tag_name']){
                    $tag->tag_name=$t['tag_name'];
                    $tag->save();
                }
            }else if($t['tag_type']=='new'){
                $tag=new Tag();
                $tag->tag_name=$t['tag_name'];
                $tag->user_id=$user->id;
                $tag->save();
            }
        }
        $data=[
            'code'=>200,
            'message'=>'保存成功'
        ];
        return response()->json($data);
    }
    // 企业的扩展字段
    public function comcreate(){
        return view('admin/com/comcreate');
    }
    // 住户的扩展字段
    public function residentcreate(){
        return view('admin/com/residentcreate');
    }
    public function comupdate(){
        return view('admin/com/comupdate');
    }
    public function comlist1(Request $request)
    {
        $community=$request->input('community','');
        $info=[];
        if($community){
            $info['name']=Area::where('id',$community)->first()->name;
            $info['total']=Company::where('enterprise','<>',3)->where('community',$community)->count();
            $info['indistrict']=Company::where('enterprise','<>',3)->where('community',$community)->where('district1',370212)->count();
            $info['outdistrict']=Company::where('enterprise','<>',3)->where('community',$community)->where('district1','<>',370212)->whereNotNull('district1')->count();
            $info['y15']=Company::where('enterprise','<>',3)->where('community',$community)->sum('yhqgpfifteen');
            $info['y50']=Company::where('enterprise','<>',3)->where('community',$community)->sum('yhqgpfifty');
            $info['r1']=Company::where('enterprise','<>',3)->where('community',$community)->where('yhqgpfifteen','>',0)->orWhere('yhqgpfifty','>',0)->count();
            $info['r2']=Company::where('enterprise','<>',3)->where('community',$community)->where('trqgd','>',0)->count();
            $info['r3']=Company::where('enterprise','<>',3)->where('community',$community)->where('cjrl','>',0)->count();
        }else{
            $info['name']='合计（北宅街道）';
            $info['total']=Company::where('enterprise','<>',3)->count();
            $info['indistrict']=Company::where('enterprise','<>',3)->where('district1',370212)->count();
            $info['outdistrict']=Company::where('enterprise','<>',3)->where('district1','<>',370212)->whereNotNull('district1')->count();
            $info['y15']=Company::where('enterprise','<>',3)->sum('yhqgpfifteen');
            $info['y50']=Company::where('enterprise','<>',3)->sum('yhqgpfifty');
            $info['r1']=Company::where('enterprise','<>',3)->where('yhqgpfifteen','>',0)->orWhere('yhqgpfifty','>',0)->count();
            $info['r2']=Company::where('enterprise','<>',3)->where('trqgd','>',0)->count();
            $info['r3']=Company::where('enterprise','<>',3)->where('cjrl','>',0)->count();
        }

        return view('admin/com/comlist1',compact('community','info'));
    }
    public function getfieldresident(){
        $res=FieldResident::orderBy('weight','desc')->get();
        return Response()->json(['code'=>1,'msg'=>'请求成功','data'=>$res]);
    }
    public function editfieldresident(Request $req){
        $data=$req->all();
        
        if(!$data['field_name'] || !$data['id']){
            return Response()->json(['code'=>0,'msg'=>'信息不完善，无法修改']);
        }
        $field=FieldResident::findOrFail($data['id']);
        $field->field_name=$data['field_name'];
        $field->weight=$data['weight'];
        $field->type=$data['type'];
        $field->is_require=$data['is_require'];
        $field->save();
        return Response()->json(['code'=>1,'msg'=>'修改成功']);
    }
    public function addfieldresident(Request $req){
        $data=$req->all();
        
        if(!$data['field_name']){
            return Response()->json(['code'=>0,'msg'=>'字段名称不能为空']);
        }
        $field=new Fieldresident;
        $field->field_name=$data['field_name'];
        $field->weight=$data['weight'];
        $field->type=$data['type'];
        $field->is_require=$data['is_require'];
        $field->save();
        return Response()->json(['code'=>1,'msg'=>'添加成功']);
    }
    public function delfieldresident(Request $req){
        $id=$req->input('id');
        $field=FieldResident::findOrFail($id);
        $field->delete();
        return Response()->json(['code'=>1,'msg'=>'删除成功']);
    }
    public function getfieldcom(){
        $res=FieldCom::orderBy('weight','desc')->get();
        return Response()->json(['code'=>1,'msg'=>'请求成功','data'=>$res]);
    }
    public function fieldcomcreate(Request $req){
        $data=$req->all();
        $com=Company::find($data['comid']);
        if(!$com){
            return Response()->json(['code'=>0,'msg'=>'单位id不正确']);
        }
        $com->fieldinfo=json_encode($data['form']);
        $com->save();
        return Response()->json(['code'=>1,'msg'=>'完善成功']);
    }
    public function fieldresidentcreate(Request $req){
        $data=$req->all();
        $com=Company::find($data['residentid']);
        if(!$com){
            return Response()->json(['code'=>0,'msg'=>'住户id不正确']);
        }
        $com->fieldinfo=json_encode($data['form']);
        $com->save();
        return Response()->json(['code'=>1,'msg'=>'完善成功']);

    }
    public function mgetfieldcom(Request $req){
        $comid=$req->input('comid');
        $com=Company::find($comid);
        $info=json_decode($com->fieldinfo);
        // $user=Auth::user();
        $field=FieldCom::orderBy('weight','desc')->get();
        return Response()->json(['code'=>1,'msg'=>'请求成功','data'=>compact('info','field')]);
    }
    public function mgetfieldresident(Request $req){
        $residentid=$req->input('residentid');
        $com=Company::find($residentid);
        $info=json_decode($com->fieldinfo);
        // $user=Auth::user();
        $field=FieldResident::orderBy('weight','desc')->get();
        return Response()->json(['code'=>1,'msg'=>'请求成功','data'=>compact('info','field')]);
    }
    public function editfieldcom(Request $req){
        $data=$req->all();
        
        if(!$data['field_name'] || !$data['id']){
            return Response()->json(['code'=>0,'msg'=>'信息不完善，无法修改']);
        }
        $field=FieldCom::findOrFail($data['id']);
        $field->field_name=$data['field_name'];
        $field->weight=$data['weight'];
        $field->type=$data['type'];
        $field->is_require=$data['is_require'];
        $field->save();
        return Response()->json(['code'=>1,'msg'=>'修改成功']);
    }
    public function addfieldcom(Request $req){
        $data=$req->all();
        
        if(!$data['field_name']){
            return Response()->json(['code'=>0,'msg'=>'字段名称不能为空']);
        }
        $field=new FieldCom;
        $field->field_name=$data['field_name'];
        $field->weight=$data['weight'];
        $field->type=$data['type'];
        $field->is_require=$data['is_require'];
        $field->save();
        return Response()->json(['code'=>1,'msg'=>'添加成功']);
    }
    public function delfieldcom(Request $req){
        $id=$req->input('id');
        $field=FieldCom::findOrFail($id);
        $field->delete();
        return Response()->json(['code'=>1,'msg'=>'删除成功']);
    }
    // 导出冬季
    public function exportwinner(Request $request){
        $month=date('Y年m月');
        $day=date('m.d');
        $lastday=date('Y年m月d日');
        $community=$request->input('community','');
        if($community){
            $info=[];
            $info['name']=Area::where('id',$community)->first()->name;
            $info['name']=str_replace('居民','',$info['name']);
            $info['name']=str_replace('社区','',$info['name']);
            $info['name']=str_replace('委员会','',$info['name']);
            $info['name']=str_replace('居委会','',$info['name']);
            $info['total']=Company::where('enterprise',3)->where('community',$community)->count();
            $info['duju']=Company::where('enterprise',3)->where('community',$community)->whereHas('resident',function($query){
                $query->where('old_type',1);
            })->count();
            $info['meiqunuan']=Company::where('enterprise',3)->where('community',$community)->whereHas('resident',function($query){
                $query->where('warm_type',1);
            })->count();
            $info['gugua']=Company::where('enterprise',3)->where('community',$community)->whereHas('resident',function($query){
                $query->where('old_type',2);
            })->count();
            $info['alarm']=Company::where('enterprise',3)->where('community',$community)->whereHas('resident',function($query){
                $query->where('alarm_type',2);
            })->count();
            $infos=[$info];
        }else{
            $infos=[];
            $res=Company::with('place_5')->where('enterprise',3)->groupBy('community')->select('community',DB::raw('count(id) as total'))->get();
            foreach($res as $k=>$v){
                $zname=str_replace('居民','',$v->place_5->name);
                $zname=str_replace('社区','',$zname);
                $zname=str_replace('委员会','',$zname);
                $zname=str_replace('居委会','',$zname);
                $infos[$v->community]=[
                    'name'=>$zname,
                    'total'=>$v->total,
                    'duju'=>0,
                    'meiqunuan'=>0,
                    'gugua'=>0,
                    'alarm'=>0
                ];
            }
            $res1=Company::where('enterprise',3)->groupBy('community')->whereHas('resident',function($query){
                $query->where('old_type',1);
            })->select('community',DB::raw('count(id) as total'))->get();
            foreach($res1 as $k=>$v){
                $infos[$v->community]['duju']=$v->total;
            }
            $res2=Company::where('enterprise',3)->groupBy('community')->whereHas('resident',function($query){
                $query->where('warm_type',1);
            })->select('community',DB::raw('count(id) as total'))->get();
            foreach($res2 as $k=>$v){
                $infos[$v->community]['meiqunuan']=$v->total;
            }
            $res3=Company::where('enterprise',3)->groupBy('community')->whereHas('resident',function($query){
                $query->where('old_type',2);
            })->select('community',DB::raw('count(id) as total'))->get();
            foreach($res3 as $k=>$v){
                $infos[$v->community]['gugua']=$v->total;
            }
            $res4=Company::where('enterprise',3)->groupBy('community')->whereHas('resident',function($query){
                $query->where('alarm_type',2);
            })->select('community',DB::raw('count(id) as total'))->get();
            foreach($res4 as $k=>$v){
                $infos[$v->community]['alarm']=$v->total;
            }
            $infos=array_values($infos);
            $zong=[
                'name'=>'合计',
                'total'=>0,
                'duju'=>0,
                'meiqunuan'=>0,
                'gugua'=>0,
                'alarm'=>0
            ];
            foreach($infos as $info){
                $zong['total']+=$info['total'];
                $zong['duju']+=$info['duju'];
                $zong['meiqunuan']+=$info['meiqunuan'];
                $zong['gugua']+=$info['gugua'];
                $zong['alarm']+=$info['alarm'];
            }
            $infos[]=$zong;
            
        }



        $templet_path=public_path('word/template/winner.docx');
        $templateProcessor  =  new \PhpOffice\PhpWord\TemplateProcessor($templet_path);
        $templateProcessor->setValue('month',$month);
        $templateProcessor->setValue('day',$day);
        $templateProcessor->setValue('lastday',$lastday);

        $count = count($infos);
        $templateProcessor->cloneRow('index',$count);
        if (is_array($infos) && count($infos) > 0) {
            if (!empty($infos)) {
                foreach ($infos as $k => $v) {
                    $index = $k + 1;
                    $templateProcessor->setValue("index#${index}", $index);
                    $templateProcessor->setValue("name#${index}", $v['name']);
                    $templateProcessor->setValue("total#${index}", $v['total']);
                    $templateProcessor->setValue("meiqunuan#${index}", $v['meiqunuan']);
                    $templateProcessor->setValue("gugua#${index}", $v['gugua']);
                    $templateProcessor->setValue("duju#${index}", $v['duju']);
                    $templateProcessor->setValue("alarm#${index}", $v['alarm']);
                }
            }
        }

        $export_name='北宅街道冬季取暖统计表.docx';
        $export_path=public_path("word/".$export_name);
        $templateProcessor->saveAs($export_path);
        return response()->download($export_path);
    }
    // 导出煤气
    public function exportmeiqi(Request $request){
        $month=date('Y年m月');
        $day=date('Y.m.d');
        $lastday=date('Y年m月d日');
        $community=$request->input('community','');
        if($community){
            $info=[];
            $info['name']=Area::where('id',$community)->first()->name;
            $info['name']=str_replace('居民','',$info['name']);
            $info['name']=str_replace('社区','',$info['name']);
            $info['name']=str_replace('委员会','',$info['name']);
            $info['name']=str_replace('居委会','',$info['name']);
            $info['total']=Company::where('enterprise',3)->where('community',$community)->count();

            $info['meiqi']=Company::where('enterprise',3)->where('community',$community)->whereHas('resident',function($query){
                $query->where('fire_type',1);
            })->count();
            $info['tianranqi']=Company::where('enterprise',3)->where('community',$community)->whereHas('resident',function($query){
                $query->where('fire_type',2);
            })->count();
            // 租赁
            $num1=Company::join('resident','company.id','=','resident.com_id')->where('enterprise',3)->where('community',$community)->whereHas('resident')->sum('resident.fire5_num1');
            $num2=Company::join('resident','company.id','=','resident.com_id')->where('enterprise',3)->where('community',$community)->whereHas('resident')->sum('resident.fire15_num1');
            $num3=Company::join('resident','company.id','=','resident.com_id')->where('enterprise',3)->where('community',$community)->whereHas('resident')->sum('resident.fire50_num1');
            $info['zulin']=$num1+$num2+$num3;
            // 购买
            $num4=Company::join('resident','company.id','=','resident.com_id')->where('enterprise',3)->where('community',$community)->whereHas('resident')->sum('resident.fire5_num2');
            $num5=Company::join('resident','company.id','=','resident.com_id')->where('enterprise',3)->where('community',$community)->whereHas('resident')->sum('resident.fire15_num2');
            $num6=Company::join('resident','company.id','=','resident.com_id')->where('enterprise',3)->where('community',$community)->whereHas('resident')->sum('resident.fire50_num2');
            $info['goumai']=$num4+$num5+$num6;
            $info['zong']=$info['zulin']+$info['goumai'];

            $infos=[$info];
        }else{
            $infos=[];
            $res=Company::with('place_5')->where('enterprise',3)->groupBy('community')->select('community',DB::raw('count(id) as total'))->get();
            foreach($res as $k=>$v){
                $zname=str_replace('居民','',$v->place_5->name);
                $zname=str_replace('社区','',$zname);
                $zname=str_replace('委员会','',$zname);
                $zname=str_replace('居委会','',$zname);
                $infos[$v->community]=[
                    'name'=>$zname,
                    'total'=>$v->total,
                    'meiqi'=>0,
                    'tianranqi'=>0,
                    'zulin'=>0,
                    'goumai'=>0,
                    'zong'=>0
                ];
            }
            $res1=Company::where('enterprise',3)->groupBy('community')->whereHas('resident',function($query){
                $query->where('fire_type',1);
            })->select('community',DB::raw('count(id) as total'))->get();
            foreach($res1 as $k=>$v){
                $infos[$v->community]['meiqi']=$v->total;
            }
            $res2=Company::where('enterprise',3)->groupBy('community')->whereHas('resident',function($query){
                $query->where('fire_type',2);
            })->select('community',DB::raw('count(id) as total'))->get();
            foreach($res2 as $k=>$v){
                $infos[$v->community]['tianranqi']=$v->total;
            }
            // 租赁
            $res1=Company::join('resident','company.id','=','resident.com_id')->where('enterprise',3)->groupBy('community')->whereHas('resident')->select('community',DB::raw('sum(resident.fire5_num1) as total'))->get();
            foreach($res1 as $k=>$v){
                if($v->total){
                    $infos[$v->community]['zulin']+=$v->total;
                }
            }
            $res1=Company::join('resident','company.id','=','resident.com_id')->where('enterprise',3)->groupBy('community')->whereHas('resident')->select('community',DB::raw('sum(resident.fire15_num1) as total'))->get();
            foreach($res1 as $k=>$v){
                if($v->total){
                    $infos[$v->community]['zulin']+=$v->total;
                }
            }
            $res1=Company::join('resident','company.id','=','resident.com_id')->where('enterprise',3)->groupBy('community')->whereHas('resident')->select('community',DB::raw('sum(resident.fire50_num1) as total'))->get();
            foreach($res1 as $k=>$v){
                if($v->total){
                    $infos[$v->community]['zulin']+=$v->total;
                }
            }
            $res1=Company::join('resident','company.id','=','resident.com_id')->where('enterprise',3)->groupBy('community')->whereHas('resident')->select('community',DB::raw('sum(resident.fire5_num2) as total'))->get();
            foreach($res1 as $k=>$v){
                if($v->total){
                    $infos[$v->community]['goumai']+=$v->total;
                }
            }
            $res1=Company::join('resident','company.id','=','resident.com_id')->where('enterprise',3)->groupBy('community')->whereHas('resident')->select('community',DB::raw('sum(resident.fire15_num2) as total'))->get();
            foreach($res1 as $k=>$v){
                if($v->total){
                    $infos[$v->community]['goumai']+=$v->total;
                }
            }
            $res1=Company::join('resident','company.id','=','resident.com_id')->where('enterprise',3)->groupBy('community')->whereHas('resident')->select('community',DB::raw('sum(resident.fire50_num2) as total'))->get();
            foreach($res1 as $k=>$v){
                if($v->total){
                    $infos[$v->community]['goumai']+=$v->total;
                }
            }

            $infos=array_values($infos);
            $zong=[
                'name'=>'合计',
                'total'=>0,
                'meiqi'=>0,
                'tianranqi'=>0,
                'zulin'=>0,
                'goumai'=>0,
                'zong'=>0
            ];
            foreach($infos as $zk=>&$info){
                $zong['total']+=$info['total'];
                $zong['meiqi']+=$info['meiqi'];
                $zong['tianranqi']+=$info['tianranqi'];
                $zong['zulin']+=$info['zulin'];
                $zong['goumai']+=$info['goumai'];
                $info['zong']=$info['zulin']+$info['goumai'];
                $zong['zong']+=$info['zong'];

            }
            $infos[]=$zong;
            
        }

        $templet_path=public_path('word/template/meiqi.docx');
        $templateProcessor  =  new \PhpOffice\PhpWord\TemplateProcessor($templet_path);
        $templateProcessor->setValue('month',$month);
        $templateProcessor->setValue('day',$day);
        $templateProcessor->setValue('lastday',$lastday);

        $count = count($infos);
        $templateProcessor->cloneRow('index',$count);
        if (is_array($infos) && count($infos) > 0) {
            if (!empty($infos)) {
                foreach ($infos as $k => $v) {
                    $index = $k + 1;
                    $templateProcessor->setValue("index#${index}", $index);
                    $templateProcessor->setValue("name#${index}", $v['name']);
                    $templateProcessor->setValue("total#${index}", $v['total']);
                    $templateProcessor->setValue("meiqi#${index}", $v['meiqi']);
                    $templateProcessor->setValue("tianranqi#${index}", $v['tianranqi']);
                    $templateProcessor->setValue("zong#${index}", $v['zong']);
                    $templateProcessor->setValue("zulin#${index}", $v['zulin']);
                    $templateProcessor->setValue("goumai#${index}", $v['goumai']);
                }
            }
        }

        $export_name='北宅街道冬季社区用煤气用户统计表.docx';
        $export_path=public_path("word/".$export_name);
        $templateProcessor->saveAs($export_path);
        return response()->download($export_path);
    }
    public function exportnum1(Request $request){
        $community=$request->input('community','');
        $query1=Company::with('commanager','safeOfficer','place_1','place_2','place_3','place_4','place_5','place_11','place_22','place_33','place_44','place_55')->where('enterprise','<>',3);
        if($community){
            $query1->where('community',$community);
        }
        $coms=$query1->where('district1',370212)->get();
        //开始写入
        $excel = new \PHPExcel();
        $excel->getDefaultStyle()->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
        $objActSheet = $excel->getActiveSheet();
        $letter = array('A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V');
        $objActSheet->setCellValue("A1","崂山区注册单位统计");
        $objActSheet->mergeCells('A1:V1');
        $objActSheet->getStyle('A1')->getAlignment()->setWrapText(true);
        $objActSheet->getStyle('A1')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);//左右居中
        $objActSheet->getStyle('A1')->getFont()->setName('黑体');
        $objActSheet->getStyle('A1')->getFont()->setSize(20);
        $objActSheet->getStyle('A1')->getFont()->setBold(true);
        //part1
        $objActSheet->setCellValue("A2","单位名称");
        $objActSheet->setCellValue("B2","经营地址");
        $objActSheet->setCellValue("C2","注册地址");
        $objActSheet->setCellValue("D2","邮政编码");
        $objActSheet->setCellValue("E2","法定代表人姓名");
        $objActSheet->setCellValue("F2","法定代表人电话");
        $objActSheet->setCellValue("G2","企业陪同人员");
        $objActSheet->setCellValue("H2","员工人数");
        $objActSheet->setCellValue("I2","占地面积");
        $objActSheet->setCellValue("J2","主要负责人员姓名");
        $objActSheet->setCellValue("K2","主要负责人员电话");
        $objActSheet->setCellValue("L2","主要负责人员微信");
        $objActSheet->setCellValue("M2","安全管理人员姓名");
        $objActSheet->setCellValue("N2","安全管理人员电话");
        $objActSheet->setCellValue("O2","液化气钢瓶15KG");
        $objActSheet->setCellValue("P2","液化气钢瓶50KG");
        $objActSheet->setCellValue("Q2","天然气管道");
        $objActSheet->setCellValue("R2","醇基燃料");
        $objActSheet->setCellValue("S2","技术服务机构人员");
        $objActSheet->setCellValue("T2","建筑面积");
        $objActSheet->setCellValue("U2","公司位置纬度");
        $objActSheet->setCellValue("V2","公司位置经度");

        $orirow = 3;
        foreach ($coms as $com){
            $objActSheet->setCellValue("A".$orirow,$com->comName);
            $str1='';
            if($com->place_1){
                $str1.=$com->place_1->name;
            }
            if($com->place_2){
                $str1.=$com->place_2->name;
            }
            if($com->place_3){
                $str1.=$com->place_3->name;
            }
            if($com->place_4){
                $str1.=$com->place_4->name;
            }
            if($com->place_5){
                $str1.=$com->place_5->name;
            }
            $str1.=$com->address;
            $objActSheet->setCellValue("B".$orirow,$str1);

            $str2='';
            if($com->place_11){
                $str2.=$com->place_11->name;
            }
            if($com->place_22){
                $str2.=$com->place_22->name;
            }
            if($com->place_33){
                $str2.=$com->place_33->name;
            }
            if($com->place_44){
                $str2.=$com->place_44->name;
            }
            if($com->place_55){
                $str2.=$com->place_55->name;
            }
            $str2.=$com->address1?$com->address1:'';
            $objActSheet->setCellValue("C".$orirow,$str2);
            $objActSheet->setCellValue("D".$orirow,$com->postcode);
            $objActSheet->setCellValue("E".$orirow,$com->leader);
            $objActSheet->setCellValue("F".$orirow,$com->leaderPhone);
            $objActSheet->setCellValue("G".$orirow,$com->accompany);
            $objActSheet->setCellValue("H".$orirow,$com->workersNum);
            $objActSheet->setCellValue("I".$orirow,$com->space);

            $objActSheet->setCellValue("J".$orirow,($com->commanager && count($com->commanager)>0)?$com->commanager[0]->name:'');
            $objActSheet->setCellValue("K".$orirow,($com->commanager && count($com->commanager)>0)?$com->commanager[0]->phone:'');
            $objActSheet->setCellValue("L".$orirow,($com->commanager && count($com->commanager)>0)?$com->commanager[0]->wx:'');
            $objActSheet->setCellValue("M".$orirow,($com->safe_officer && count($com->safe_officer)>0)?$com->safe_officer[0]->name:'');
            $objActSheet->setCellValue("N".$orirow,($com->safe_officer && count($com->safe_officer)>0)?$com->safe_officer[0]->phone:'');
            $objActSheet->setCellValue("O".$orirow,$com->yhqgpfifteen);
            $objActSheet->setCellValue("P".$orirow,$com->yhqgpfifty);
            $objActSheet->setCellValue("Q".$orirow,$com->trqgd==1?'是':'否');
            $objActSheet->setCellValue("R".$orirow,$com->cjrl==1?'是':'否');
            $objActSheet->setCellValue("S".$orirow,$com->service);
            $objActSheet->setCellValue("T".$orirow,$com->area);
            $objActSheet->setCellValue("U".$orirow,$com->lat);
            $objActSheet->setCellValue("V".$orirow,$com->lng);



            $orirow++;
        }
        //统一设置填充内容左右居中、字号、宽度
        foreach ($letter as $l){
            for ($x = 2;$x<=$orirow;$x++){
                $objActSheet->getStyle($l.$x)->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);//左右居中
                $objActSheet->getStyle($l.$x)->getFont()->setSize(10);
            }

        }
        for ($x=1; $x<=($orirow+10); $x++) {
            $objActSheet->getRowDimension($x)->setRowHeight(20);//统一设置行高度

        }
        $styleArray = array(
            'borders' => array(
                'allborders' => array(
                    'style' => \PHPExcel_Style_Border::BORDER_THIN,//细边框
                ),
            ),
        );
        $objActSheet->getColumnDimension('A')->setWidth(30);
        $objActSheet->getColumnDimension('B')->setWidth(60);
        $objActSheet->getColumnDimension('C')->setWidth(60);

        $objActSheet->getStyle('A1:V'.($orirow+10))->applyFromArray($styleArray);//设置粗边框
        $objActSheet->getRowDimension(1)->setRowHeight(30);//单独设置行高度
        $write = new \PHPExcel_Writer_Excel5($excel);
        ob_end_clean();
        header("Pragma: public");
        header("Expires: 0");
        header("Cache-Control:must-revalidate, post-check=0, pre-check=0");
        header("Content-Type:application/force-download");
        header("Content-Type:application/vnd.ms-execl");
        header("Content-Type:application/octet-stream");
        header("Content-Type:application/download");;
        header('Content-Disposition:attachment;filename=崂山区注册单位统计表.xls');
        header("Content-Transfer-Encoding:binary");
        $write->save('php://output');
    }
    public function exportnum2(Request $request){
        $community=$request->input('community','');
        $query1=Company::with('commanager','safeOfficer','place_1','place_2','place_3','place_4','place_5','place_11','place_22','place_33','place_44','place_55')->where('enterprise','<>',3);
        if($community){
            $query1->where('community',$community);
        }
        $coms=$query1->where('district1','<>',370212)->whereNotNull('district1')->get();
        //开始写入
        $excel = new \PHPExcel();
        $excel->getDefaultStyle()->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
        $objActSheet = $excel->getActiveSheet();
        $letter = array('A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V');
        $objActSheet->setCellValue("A1","非崂山区注册单位统计");
        $objActSheet->mergeCells('A1:V1');
        $objActSheet->getStyle('A1')->getAlignment()->setWrapText(true);
        $objActSheet->getStyle('A1')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);//左右居中
        $objActSheet->getStyle('A1')->getFont()->setName('黑体');
        $objActSheet->getStyle('A1')->getFont()->setSize(20);
        $objActSheet->getStyle('A1')->getFont()->setBold(true);
        //part1
        $objActSheet->setCellValue("A2","单位名称");
        $objActSheet->setCellValue("B2","经营地址");
        $objActSheet->setCellValue("C2","注册地址");
        $objActSheet->setCellValue("D2","邮政编码");
        $objActSheet->setCellValue("E2","法定代表人姓名");
        $objActSheet->setCellValue("F2","法定代表人电话");
        $objActSheet->setCellValue("G2","企业陪同人员");
        $objActSheet->setCellValue("H2","员工人数");
        $objActSheet->setCellValue("I2","占地面积");
        $objActSheet->setCellValue("J2","主要负责人员姓名");
        $objActSheet->setCellValue("K2","主要负责人员电话");
        $objActSheet->setCellValue("L2","主要负责人员微信");
        $objActSheet->setCellValue("M2","安全管理人员姓名");
        $objActSheet->setCellValue("N2","安全管理人员电话");
        $objActSheet->setCellValue("O2","液化气钢瓶15KG");
        $objActSheet->setCellValue("P2","液化气钢瓶50KG");
        $objActSheet->setCellValue("Q2","天然气管道");
        $objActSheet->setCellValue("R2","醇基燃料");
        $objActSheet->setCellValue("S2","技术服务机构人员");
        $objActSheet->setCellValue("T2","建筑面积");
        $objActSheet->setCellValue("U2","公司位置纬度");
        $objActSheet->setCellValue("V2","公司位置经度");

        $orirow = 3;
        foreach ($coms as $com){
            $objActSheet->setCellValue("A".$orirow,$com->comName);
            $str1='';
            if($com->place_1){
                $str1.=$com->place_1->name;
            }
            if($com->place_2){
                $str1.=$com->place_2->name;
            }
            if($com->place_3){
                $str1.=$com->place_3->name;
            }
            if($com->place_4){
                $str1.=$com->place_4->name;
            }
            if($com->place_5){
                $str1.=$com->place_5->name;
            }
            $str1.=$com->address;
            $objActSheet->setCellValue("B".$orirow,$str1);

            $str2='';
            if($com->place_11){
                $str2.=$com->place_11->name;
            }
            if($com->place_22){
                $str2.=$com->place_22->name;
            }
            if($com->place_33){
                $str2.=$com->place_33->name;
            }
            if($com->place_44){
                $str2.=$com->place_44->name;
            }
            if($com->place_55){
                $str2.=$com->place_55->name;
            }
            $str2.=$com->address1?$com->address1:'';
            $objActSheet->setCellValue("C".$orirow,$str2);
            $objActSheet->setCellValue("D".$orirow,$com->postcode);
            $objActSheet->setCellValue("E".$orirow,$com->leader);
            $objActSheet->setCellValue("F".$orirow,$com->leaderPhone);
            $objActSheet->setCellValue("G".$orirow,$com->accompany);
            $objActSheet->setCellValue("H".$orirow,$com->workersNum);
            $objActSheet->setCellValue("I".$orirow,$com->space);

            $objActSheet->setCellValue("J".$orirow,($com->commanager && count($com->commanager)>0)?$com->commanager[0]->name:'');
            $objActSheet->setCellValue("K".$orirow,($com->commanager && count($com->commanager)>0)?$com->commanager[0]->phone:'');
            $objActSheet->setCellValue("L".$orirow,($com->commanager && count($com->commanager)>0)?$com->commanager[0]->wx:'');
            $objActSheet->setCellValue("M".$orirow,($com->safe_officer && count($com->safe_officer)>0)?$com->safe_officer[0]->name:'');
            $objActSheet->setCellValue("N".$orirow,($com->safe_officer && count($com->safe_officer)>0)?$com->safe_officer[0]->phone:'');
            $objActSheet->setCellValue("O".$orirow,$com->yhqgpfifteen);
            $objActSheet->setCellValue("P".$orirow,$com->yhqgpfifty);
            $objActSheet->setCellValue("Q".$orirow,$com->trqgd==1?'是':'否');
            $objActSheet->setCellValue("R".$orirow,$com->cjrl==1?'是':'否');
            $objActSheet->setCellValue("S".$orirow,$com->service);
            $objActSheet->setCellValue("T".$orirow,$com->area);
            $objActSheet->setCellValue("U".$orirow,$com->lat);
            $objActSheet->setCellValue("V".$orirow,$com->lng);



            $orirow++;
        }
        //统一设置填充内容左右居中、字号、宽度
        foreach ($letter as $l){
            for ($x = 2;$x<=$orirow;$x++){
                $objActSheet->getStyle($l.$x)->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);//左右居中
                $objActSheet->getStyle($l.$x)->getFont()->setSize(10);
            }

        }
        for ($x=1; $x<=($orirow+10); $x++) {
            $objActSheet->getRowDimension($x)->setRowHeight(20);//统一设置行高度

        }
        $styleArray = array(
            'borders' => array(
                'allborders' => array(
                    'style' => \PHPExcel_Style_Border::BORDER_THIN,//细边框
                ),
            ),
        );
        $objActSheet->getColumnDimension('A')->setWidth(30);
        $objActSheet->getColumnDimension('B')->setWidth(60);
        $objActSheet->getColumnDimension('C')->setWidth(60);

        $objActSheet->getStyle('A1:V'.($orirow+10))->applyFromArray($styleArray);//设置粗边框
        $objActSheet->getRowDimension(1)->setRowHeight(30);//单独设置行高度
        $write = new \PHPExcel_Writer_Excel5($excel);
        ob_end_clean();
        header("Pragma: public");
        header("Expires: 0");
        header("Cache-Control:must-revalidate, post-check=0, pre-check=0");
        header("Content-Type:application/force-download");
        header("Content-Type:application/vnd.ms-execl");
        header("Content-Type:application/octet-stream");
        header("Content-Type:application/download");;
        header('Content-Disposition:attachment;filename=非崂山区注册单位统计表.xls');
        header("Content-Transfer-Encoding:binary");
        $write->save('php://output');
    }
    public function comlist1_pro(Request $quest)
    {
        $tree = array();
        $area_first = array();
        $area_first = Area::where('name','北宅街道')->first()->toArray();
        $tree = $area_first;
        $where = array();
        $where[] = array('pid', $area_first['id']);
        $area = Area::where($where)->select(array('id','pid','node','name','level'))->get()->toArray();
        $tree['street'] = $area;
        return view('admin/com/comlist1_pro')->with('tree',$tree);
    }
    public function comlist2(Request $quest)
    {
        return view('admin/com/comlist2');
    }
    public function residentlist(Request $quest)
    {
        return view('admin/com/residentlist');
    }
    public function residentlist1(Request $req)
    {
        $community=$req->input('community','');
        $info=[];
        if($community){
            $info['name']=Area::where('id',$community)->first()->name;
            $info['total']=Company::where('enterprise',3)->where('community',$community)->count();
            $info['duju']=Company::where('enterprise',3)->where('community',$community)->whereHas('resident',function($query){
                $query->where('old_type',1);
            })->count();
            $info['gugua']=Company::where('enterprise',3)->where('community',$community)->whereHas('resident',function($query){
                $query->where('old_type',2);
            })->count();
            $info['meiqi']=Company::where('enterprise',3)->where('community',$community)->whereHas('resident',function($query){
                $query->where('fire_type',1);
            })->count();
            $info['tianran']=Company::where('enterprise',3)->where('community',$community)->whereHas('resident',function($query){
                $query->where('fire_type',2);
            })->count();
            // 租赁
            $num1=Company::join('resident','company.id','=','resident.com_id')->where('enterprise',3)->where('community',$community)->whereHas('resident')->sum('resident.fire5_num1');
            $num2=Company::join('resident','company.id','=','resident.com_id')->where('enterprise',3)->where('community',$community)->whereHas('resident')->sum('resident.fire15_num1');
            $num3=Company::join('resident','company.id','=','resident.com_id')->where('enterprise',3)->where('community',$community)->whereHas('resident')->sum('resident.fire50_num1');
            $info['zulin']=$num1+$num2+$num3;
            // 购买
            $num4=Company::join('resident','company.id','=','resident.com_id')->where('enterprise',3)->where('community',$community)->whereHas('resident')->sum('resident.fire5_num2');
            $num5=Company::join('resident','company.id','=','resident.com_id')->where('enterprise',3)->where('community',$community)->whereHas('resident')->sum('resident.fire15_num2');
            $num6=Company::join('resident','company.id','=','resident.com_id')->where('enterprise',3)->where('community',$community)->whereHas('resident')->sum('resident.fire50_num2');
            $info['goumai']=$num4+$num5+$num6;
            $info['zong']=$info['zulin']+$info['goumai'];
        }else{
            $info['name']='合计（北宅街道）';
            $info['total']=Company::where('enterprise',3)->count();
            $info['duju']=Company::where('enterprise',3)->whereHas('resident',function($query){
                $query->where('old_type',1);
            })->count();
            $info['gugua']=Company::where('enterprise',3)->whereHas('resident',function($query){
                $query->where('old_type',2);
            })->count();
            $info['meiqi']=Company::where('enterprise',3)->whereHas('resident',function($query){
                $query->where('fire_type',1);
            })->count();
            $info['tianran']=Company::where('enterprise',3)->whereHas('resident',function($query){
                $query->where('fire_type',2);
            })->count();
            // 租赁
            $num1=Company::join('resident','company.id','=','resident.com_id')->where('enterprise',3)->whereHas('resident')->sum('resident.fire5_num1');
            $num2=Company::join('resident','company.id','=','resident.com_id')->where('enterprise',3)->whereHas('resident')->sum('resident.fire15_num1');
            $num3=Company::join('resident','company.id','=','resident.com_id')->where('enterprise',3)->whereHas('resident')->sum('resident.fire50_num1');
            $info['zulin']=$num1+$num2+$num3;
            // 购买
            $num4=Company::join('resident','company.id','=','resident.com_id')->where('enterprise',3)->whereHas('resident')->sum('resident.fire5_num2');
            $num5=Company::join('resident','company.id','=','resident.com_id')->where('enterprise',3)->whereHas('resident')->sum('resident.fire15_num2');
            $num6=Company::join('resident','company.id','=','resident.com_id')->where('enterprise',3)->whereHas('resident')->sum('resident.fire50_num2');
            $info['goumai']=$num4+$num5+$num6;
            $info['zong']=$info['zulin']+$info['goumai'];
        }
        return view('admin/com/residentlist1')->with('community',$req->input('community',''))->with('info',$info);
    }
    public function residentlist1_pro(Request $quest)
    {
        $tree = array();
        $area_first = array();
        $area_first = Area::where('name','北宅街道')->first()->toArray();
        $tree = $area_first;
        $where = array();
        $where[] = array('pid', $area_first['id']);
        $area = Area::where($where)->select(array('id','pid','node','name','level'))->get()->toArray();
        $tree['street'] = $area;
        return view('admin/com/residentlist1_pro')->with('tree',$tree);
    }
    public function residentlist2(Request $quest)
    {
        return view('admin/com/residentlist2');
    }
    public function msglist(Request $quest)
    {
        return view('admin/msg/msglist');
    }
    public function report(Request $request){
        return view('admin/user/report',['userid'=>$request->input('id')]);
    }
    public function comfield(){
        return view('admin/user/system',['title'=>'企业字段设置','component'=>'field1']);
    }
    public function residentfield(){
        return view('admin/user/system',['title'=>'住户字段设置','component'=>'field2']);
    }
    // 获取 日报列表
    public function reportlist(Request $request){
        // $query=Report::with('userinfo')->where('user_id',$request->input('userid'))->orderBy('created_at','desc');
        // if(isset($request['keyword'])){
        //     $query->where('title','like','%'.$request['keyword'].'%')->orWhere('content','like','%'.$request['keyword'].'%');
        // }



        $query=StaffSumary::with('userinfo')->where('userid',$request->input('userid'))->orderBy('time','desc');
        if(isset($request['keyword'])){
            $query->where('summary','like','%'.$request['keyword'].'%');
        }

        $res=$query->paginate($request->get('limit',15))->toArray();
        foreach($res['data'] as &$v){
            $v['day']=date('Y-m-d',$v['time']);
        }
        $data = [
            'code' => 0,
            'msg'   => '请求成功',
            'count' => $res['total'],
            'data'  => $res['data']
        ];
        return response()->json($data);
    }
    // 物资统计分析
    public function tongji(){
        // dd(1);
        return view('admin/user/system',['title'=>'物资统计分析','component'=>'tongji']);
    }
    public function tongjidata(){
        $user=Auth::guard('admin')->user();
        if($user->role=='community_admin'){
            // 社区管理员
            $community=$user->community;
            $res=Stock::with('goods')->whereHas('goods')->whereHas('company', function ($query)use($community) {
                $query->where('community',$community);
            })->select(DB::raw('goods_id,sum(income_warehouse) as income_warehouse,sum(leave_warehouse) as leave_warehouse,sum(loss_warehouse) as loss_warehouse,sum(warehouse) as warehouse'))->groupBy('goods_id')->get();
        }else{
           // $res=Goods::get(); 
            $res=Stock::with('goods')->whereHas('goods')->select(DB::raw('goods_id,sum(income_warehouse) as income_warehouse,sum(leave_warehouse) as leave_warehouse,sum(loss_warehouse) as loss_warehouse,sum(warehouse) as warehouse'))->groupBy('goods_id')->get();
        }
        
        
        $data = [
            'code' => 1,
            'msg'   => '请求成功',
            'data'  => $res
        ];
        return response()->json($data);
    }
    public function daping_warehouse(){
        $res = Goods::where('pid', 0)->orderBy('warehouse','desc')->get();



        // $res=Stock::with('goods')->whereHas('goods')->select(DB::raw('goods_id,sum(income_warehouse) as income_warehouse,sum(leave_warehouse) as leave_warehouse,sum(loss_warehouse) as loss_warehouse,sum(warehouse) as warehouse'))->groupBy('goods_id')->get();
        $kucun=0;
        $in=0;
        $out=0;
        // $res = $res->sortByDesc('warehouse')->values()->all();
        foreach($res as $r){
            $kucun+=$r->warehouse;
            $out+=$r->leave_warehouse;
            $in+=$r->income_warehouse;
        }
        $data = [
            'code' => 1,
            'msg'   => '请求成功',
            'data'  => array('list'=>$res,'kucun'=>$kucun,'out'=>$out,'in'=>$in)
        ];
        return response()->json($data);
    }
    public function system(){
        return view('admin/user/system',['title'=>'系统管理','component'=>'system','key1'=>'pid','val1'=>1,'key2'=>'isshow','val2'=>'false']);
    }
    public function module1(){
        return view('admin/user/system',['title'=>'安全生产基础数据','component'=>'module1']);
    }
    public function module2(){
        return view('admin/user/system',['title'=>'智能管理','component'=>'module2']);
    }
    public function module3(){
        return view('admin/user/system',['title'=>'应急物资储备','component'=>'module3']);
    }
    public function module4(){
        return view('admin/user/system',['title'=>'安全生产教育培训','component'=>'module4']);
    }
    public function module5(){
        return view('admin/user/system',['title'=>'企业自查自纠','component'=>'module5']);
    }
    public function module6(){
        return view('admin/user/system',['title'=>'消息模块','component'=>'module6']);
    }
    public function warehouse(){
        return view('admin/user/system',['title'=>'仓库管理','component'=>'warehouse']);
    }
    public function useredit(Request $request){
        $user=User::findorfail($request['id']);
        if ($request->isMethod('post')) {
            if($user->type==1){
                $this->validate($request, [
                    'username' => 'required|min:2',
                    //'sex' => 'required',
                    'mwork' => 'required',
                    'moffwork' => 'required',
                    'awork' => 'required',
                    'aoffwork' => 'required',
                    //'id_card'  => 'required|max:18|min:18'
                ],[
                    'username.required' => '名称必填',
                    //'id_card.required' => '身份证号必填',
                    'username.min' => '名称不符合规范',
                    'id_card.max' => '身份证号格式不正确',
                    'id_card.min' => '身份证号格式不正确'
                ]);
                $user->name=$request['username'];
                // $user->cardnum=$request['cardnum'];
                $user->sex=$request['sex'];
                $user->department_id=$request['department_id'];
                $user->job_id=$request['job_id'];
                $user->idcard=$request['id_card'];
                $user->phone=$request['phone'];
                if($request['id_card']){
                    $user->account=$request['id_card'];
                }
                $user->duty=$request['duty'];
                $user->mwork=$request['mwork'];
                $user->moffwork=$request['moffwork'];
                $user->awork=$request['awork'];
                $user->aoffwork=$request['aoffwork'];
                $user->province=$request['province']?$request['province']:null;
                $user->city=$request['city']?$request['city']:null;
                $user->district=$request['district']?$request['district']:null;
                $user->street=$request['street']?$request['street']:null;
                $user->community=$request['community']?$request['community']:null;
                $user->save();
                return redirect()->to(route('admin.user.userlist'))->with(['status'=>'修改成功']);
            }elseif($user->type==2){
                $this->validate($request, [
                    'qyname' => 'required|min:3',
                    'qycard' => 'required|min:10'
                ],[
                    'qyname.required' => '企业名称必填',
                    'qycard.required' => '营业执照编号必填',
                    'qyname.min' => '企业名称不符合规范',
                    'qycard.min' => '营业执照格式不正确'
                ]);
                
                $user->name=$request['qyname'];
                $user->cardnum=$request['qycard'];
                $user->save();

                $com=Company::find($user->companyId);
                $com->comName=$request['qyname'];
                $com->licensenum=$request['qycard'];
                $com->save();
                return redirect()->to(route('admin.user.userlist'))->with(['status'=>'修改成功']);
            }else{
                $this->validate($request, [
                    'username' => 'required|min:2',
                    'sex' => 'required',
                    'cardnum'  => 'required|max:18|min:18'
                ],[
                    'username.required' => '名称必填',
                    'cardnum.required' => '身份证号必填',
                    'username.min' => '名称不符合规范',
                    'cardnum.max' => '身份证号格式不正确',
                    'cardnum.min' => '身份证号格式不正确'
                ]);
                $user->name=$request['username'];
                $user->cardnum=$request['cardnum'];
                $user->sex=$request['sex'];
                $user->save();
                return redirect()->to(route('admin.user.userlist'))->with(['status'=>'修改成功']);
            }

        }
        $deps=Department::select('id','department')->get();
        $jobs=Job::select('id','job')->get();

        

        $provinces=Area::where('level',1)->select('id','name')->get();
        if($user->province){
            $citys=Area::where('level',2)->where('pid',$user->province)->select('id','name')->get();
        }else{
            $citys=[];
        }
        if($user->city){
            $districts=Area::where('level',3)->where('pid',$user->city)->select('id','name')->get();
        }else{
            $districts=[];
        }
        if($user->district){
            $streets=Area::where('level',4)->where('pid',$user->district)->select('id','name')->get();
        }else{
            $streets=[];
        }
        if($user->street){
            $communitys=Area::where('level',5)->where('pid',$user->street)->select('id','name')->get();
        }else{
            $communitys=[];
        }
        
        return view('admin/user/useredit',compact('user','deps','jobs','provinces','citys','districts','streets','communitys'));
        
    }
    public function depedit(Request $request){
        $dep=Department::find($request['id']);
        if ($request->isMethod('post')) {
            $this->validate($request, [
                'department' => 'required'
            ],[
                'department.required' => '部门名称必填'
            ]);
            
            $dep->department=$request['department'];
            $dep->remark=$request['remark'];
            $dep->save();
            return redirect()->to(route('admin.user.deplist'))->with(['status'=>'修改成功']);

        }
        return view('admin/user/depedit')->with('dep',$dep);
    }
    public function roleedit(Request $request){
        $role=AdminUserRole::find($request['id']);
        if ($request->isMethod('post')) {
            $this->validate($request, [
                'role_name' => 'required'
            ],[
                'role_name.required' => '角色名称必填'
            ]);
            
            $role->role_name=$request['role_name'];
            $role->note=$request['note'];
            $role->save();
            return redirect()->to(route('admin.user.rolelist'))->with(['status'=>'修改成功']);
        }
        return view('admin/user/roleedit')->with('role',$role);
    }
    public function jobedit(Request $request){
        $job=Job::find($request['id']);
        if ($request->isMethod('post')) {
            $this->validate($request, [
                'job' => 'required'
            ],[
                'job.required' => '岗位名称必填'
            ]);
            
            $job->job=$request['job'];
            $job->remark=$request['remark'];
            $job->save();
            return redirect()->to(route('admin.user.joblist'))->with(['status'=>'修改成功']);

        }
        return view('admin/user/jobedit')->with('job',$job);
    }
    public function adduser(Request $request){
        if ($request->isMethod('post')) {
            $this->validate($request, [
                'username' => 'required|min:2',
                //'sex' => 'required',
                'mwork' => 'required',
                'moffwork' => 'required',
                'awork' => 'required',
                'aoffwork' => 'required',
                //'id_card'  => 'required|max:18|min:18'
            ],[
                'username.required' => '名称必填',
                //'id_card.required' => '身份证号必填',
                'username.min' => '名称不符合规范',
                //'id_card.max' => '身份证号格式不正确',
                //'id_card.min' => '身份证号格式不正确'
            ]);
            $user=new User();
            $user->name=$request['username'];

            $lastuser=User::where('type','1')->orderBy('id','desc')->first();
            $lastid=intval($lastuser->account)+1;
            $user->account=$request['id_card']?$request['id_card']:$request['username'];
            $user->idcard=$request['id_card'];
            $user->phone=$request['phone'];
            $user->department_id=$request['department_id'];
            $user->job_id=$request['job_id'];
            $user->duty=$request['duty'];
            $user->mwork=$request['mwork'];
            $user->moffwork=$request['moffwork'];
            $user->awork=$request['awork'];
            $user->aoffwork=$request['aoffwork'];

            $user->sex=$request['sex'];
            $user->password=bcrypt('123456');
            $user->type = 1;
            // $user->role='normal';
            $user->province=$request['province']?$request['province']:null;
            $user->city=$request['city']?$request['city']:null;
            $user->district=$request['district']?$request['district']:null;
            $user->street=$request['street']?$request['street']:null;
            $user->community=$request['community']?$request['community']:null;
            $user->role='community_user';
            // $zrole=Auth::guard('admin')->user()->role;
            // if($zrole=='admin' || $zrole=='district_admin'){
            //     // 区管理员
            //     $user->role=$request['community']?'community_user':($request['street']?'street_user':'district_user');
            // }else if($zrole=='street_admin'){
            //     // 街道管理员
            //     $user->role=$request['community']?'community_user':'street_user';
            // }else if($zrole=='community_admin'){
            //     // 社区管理员
            //     $user->role='community_user';
            // }
            $user->save();
            return redirect()->to(route('admin.user.userlist'))->with(['status'=>'添加成功']);
        }
        $role=Auth::guard('admin')->user()->role;
        $community_code=Auth::guard('admin')->user()->community;

        $provinces=Area::where('level',1)->select('id','name')->get();
        $citys=Area::where('level',2)->where('pid',370000)->select('id','name')->get();
        $districts=Area::where('level',3)->where('pid',370200)->select('id','name')->get();
        $streets=Area::where('level',4)->where('pid',370212)->select('id','name')->get();
        $communitys=Area::where('level',5)->where('pid',370212004)->select('id','name')->get();

        
        return view('admin/user/adduser',compact('provinces','citys','districts','streets','communitys','role','community_code'));
    }
    /**
     * 权限管理
     * @param Request $request
     * @return \Illuminate\Contracts\View\Factory|\Illuminate\Foundation\Application|\Illuminate\View\View
     */
    public function role(Request $request){
        $id = $request['id'];
        $user = User::findOrFail($id);
        //获取当前登录者的权限,超级管理显示所有的
        $role = Auth::guard('admin')->user()->role;
        if($role == 'admin'){
            $roles = AdminUserMenu::get();
        }else{
            $adminId = Auth::guard('admin')->user()->id;
            $adminRelation = AdminUserMenuRelation::where('user_id',$adminId)->get()->toArray();
            $adminRolesRelation = array_column($adminRelation,'menu_id');
            $roles = AdminUserMenu::whereIn('id',$adminRolesRelation)->get();
        }

        $AdminUserMenuRelation = AdminUserMenuRelation::where('user_id',$id)->get()->toArray();
        $rolesRelation = array_column($AdminUserMenuRelation,'id','menu_id');
        foreach ($roles as $role){
            $role->own = isset($rolesRelation[$role->id]) ? true : false;
        }
        return view('admin/user/role',compact('roles','user'));

    }

    /**
     * 更新分配角色
     */
    public function assignRole(Request $request,$id)
    {
        $roles = $request->get('permissions',[]);
        $AdminUserMenuRelation = AdminUserMenuRelation::where('user_id',$id)->get()->toArray();
        $res = [];
        if ($roles && $AdminUserMenuRelation){
            $del = AdminUserMenuRelation::where('user_id', $id)->delete();
        }
        foreach ($roles as $val){
            $data['user_id'] = $id;
            $data['menu_id'] = $val;
            $res = AdminUserMenuRelation::create($data);
        }
        if($res){
            return redirect()->to(route('admin.user.userlist'))->with(['status'=>'设置用户权限成功']);
        }else{
            return redirect()->to(route('admin.user.userlist'))->withErrors('系统错误');
        }
    }
    /**
     * 角色权限管理
     * @param Request $request
     * @return \Illuminate\Contracts\View\Factory|\Illuminate\Foundation\Application|\Illuminate\View\View
     */
    public function rolemenu(Request $request){
        $id = $request['id'];
        $role = AdminUserRole::with('menus','menus2')->findOrFail($id);
        $menus = AdminRoleMenu::get();
        if($role->menus){
            foreach ($menus as $menu){
                foreach ($role->menus as $k => $v) {
                    if($v->menu_id==$menu->id){
                        $menu->own=true;
                    }
                }
            }
        }
            
        $menus2 = AdminRoleMenuM::orderBy('sort','asc')->get();
        if($role->menus2){
            foreach ($menus2 as $menu){
                foreach ($role->menus2 as $k => $v) {
                    if($v->menu_id==$menu->id){
                        $menu->own=true;
                    }
                }
            }
        }
            
        return view('admin/user/role',compact('menus','menus2','role'));

    }
    /**
     * 角色更新分配权限
     */
    public function assignRolemenu(Request $request,$id){
        $menus = $request->get('permissions',[]);
        $menus2 = $request->get('permissions2',[]);
        AdminRoleMenuRelation::where('role_id', $id)->delete();
        AdminRoleMenuMRelation::where('role_id', $id)->delete();
        $datas=[];
        foreach ($menus as $val){
            $datas[]=[
                'role_id'=>$id,
                'menu_id'=>$val
            ];
        }
        DB::table('admin_role_menu_relation')->insert($datas);
        $datas2=[];
        foreach ($menus2 as $val){
            $datas2[]=[
                'role_id'=>$id,
                'menu_id'=>$val
            ];
        }
        DB::table('admin_role_menu_m_relation')->insert($datas2);
        return redirect()->to(route('admin.user.rolelist'))->with(['status'=>'设置角色权限成功']);
    }
    public function usermenu(Request $request){
        $id = $request['id'];
        $user = User::with('menus','menus2','roles.menus','roles.menus2')->findOrFail($id);
        $menus = AdminRoleMenu::get();
        if(count($user->menus)>0){
            foreach ($menus as $menu){
                foreach ($user->menus as $k => $v) {
                    if($v->menu_id==$menu->id){
                        $menu->own=true;
                    }
                }
            }
        }else{
            $mids=[];
            foreach($user->roles as $v){
                foreach($v->menus as $v2){
                    $mids[]=$v2->menu_id;
                }
            }
            if($mids){
                foreach ($menus as $menu){
                    if(in_array($menu->id,$mids)){
                        $menu->own=true;
                    }
                }
            }
        }
            
        $menus2 = AdminRoleMenuM::orderBy('sort','asc')->get();
        if(count($user->menus2)>0){
            foreach ($menus2 as $menu){
                foreach ($user->menus2 as $k => $v) {
                    if($v->menu_id==$menu->id){
                        $menu->own=true;
                    }
                }
            }
        }else{
            $mids=[];
            foreach($user->roles as $v){
                foreach($v->menus2 as $v2){
                    $mids[]=$v2->menu_id;
                }
            }
            if($mids){
                foreach ($menus2 as $menu){
                    if(in_array($menu->id,$mids)){
                        $menu->own=true;
                    }
                }
            }
        }
            
        return view('admin/user/usermenu',compact('menus','menus2','user'));

    }
    /**
     * 用户更新分配权限
     */
    public function assignUsermenu(Request $request,$id){
        $menus = $request->get('permissions',[]);
        $menus2 = $request->get('permissions2',[]);
        AdminUserMenuRelation::where('user_id', $id)->delete();
        AdminUserMenuMRelation::where('user_id', $id)->delete();
        $datas=[];
        foreach ($menus as $val){
            $datas[]=[
                'user_id'=>$id,
                'menu_id'=>$val
            ];
        }
        DB::table('admin_user_menu_relation')->insert($datas);
        $datas2=[];
        foreach ($menus2 as $val){
            $datas2[]=[
                'user_id'=>$id,
                'menu_id'=>$val
            ];
        }
        DB::table('admin_user_menu_m_relation')->insert($datas2);
        return redirect()->to(route('admin.user.userlist'))->with(['status'=>'设置用户权限成功']);
    }
    public function getuserroleinfo(){
        $user=Auth::guard('admin')->user();
        // $roles=AdminUserRoleRelation::where('user_id',$user->id)->pluck('role_id');
        $data=[
            'code'=>1,
            'msg'=>'请求成功',
            // 'data'=>$roles
            'data'=>$user->role
        ];
        return response()->json($data);
    }
    /**
     * 用户角色管理
     * @param Request $request
     * @return \Illuminate\Contracts\View\Factory|\Illuminate\Foundation\Application|\Illuminate\View\View
     */
    public function userrole(Request $request){
        $id = $request['id'];
        $user = User::with('roles')->findOrFail($id);
        $roles = AdminUserRole::get();
        foreach ($roles as $role){
            if($user->roles){
                foreach ($user->roles as $k => $v) {
                    if($v->role_id==$role->id){
                        $role->own=true;
                    }
                }
            }
        }
        return view('admin/user/role2',compact('roles','user'));

    }
    public function userrole2(Request $request){
        $id = $request['id'];
        $user = User::findOrFail($id);
        $roles =explode(',', $user->work_type);
        return view('admin/user/role3',compact('roles','user'));
    }
    /**
     * 用户分配角色
     */
    public function assignUserrole(Request $request,$id){
        $roles = $request->get('roles',[]);
        AdminUserRoleRelation::where('user_id', $id)->delete();
        $datas=[];
        foreach ($roles as $val){
            $datas[]=[
                'user_id'=>$id,
                'role_id'=>$val
            ];
        }
        DB::table('admin_user_role_relation')->insert($datas);
        return redirect()->to(route('admin.user.userlist'))->with(['status'=>'设置社区管理员角色成功']);
    }
    /**
     * 工作人员 分配角色
     */
    public function assignUserrole2(Request $request,$id){
        $roles = $request->get('roles',[]);
        $role_str=implode(',', $roles);
        $user=User::findOrFail($id);
        $user->work_type=$role_str;
        $user->save();
        return redirect()->to(route('admin.user.userlist'))->with(['status'=>'设置工作人员角色成功']);
    }
    public function adddep(Request $request){
        if ($request->isMethod('post')) {
            $this->validate($request, [
                'department' => 'required'
            ],[
                'department.required' => '部门名称必填'
            ]);
            $dep=new Department();
            $dep->department=$request['department'];
            $dep->remark=$request['remark'];
            $dep->save();
            return redirect()->to(route('admin.user.deplist'))->with(['status'=>'添加成功']);
        }
        return view('admin/user/adddep');
    }
    // 增加角色
    public function addrole(Request $request){
        if ($request->isMethod('post')) {
            $this->validate($request, [
                'role_name' => 'required',
                // 'cardnum'  => 'required|max:18|min:18'
            ],[
                'department.required' => '角色名称必填',
                // 'cardnum.required' => '身份证号必填',
                // 'cardnum.max' => '身份证号格式不正确',
                // 'cardnum.min' => '身份证号格式不正确'
            ]);
            $dep=new AdminUserRole();
            $dep->role_name=$request['role_name'];
            $dep->note=$request['note'];
            $dep->save();
            return redirect()->to(route('admin.user.rolelist'))->with(['status'=>'添加成功']);
        }
        return view('admin/user/addrole');
    }
    public function addjob(Request $request){
        if ($request->isMethod('post')) {
            $this->validate($request, [
                'job' => 'required',
                // 'cardnum'  => 'required|max:18|min:18'
            ],[
                'job.required' => '岗位名称必填',
                // 'cardnum.required' => '身份证号必填',
                // 'cardnum.max' => '身份证号格式不正确',
                // 'cardnum.min' => '身份证号格式不正确'
            ]);
            $dep=new Job();
            $dep->job=$request['job'];
            $dep->remark=$request['remark'];
            $dep->save();
            return redirect()->to(route('admin.user.joblist'))->with(['status'=>'添加成功']);
        }
        return view('admin/user/addjob');
    }
    public function addgovuser(Request $request){
        if ($request->isMethod('post')) {
            $this->validate($request, [
                'username' => 'required|min:2',
                'sex' => 'required',
                'cardnum'  => 'required|max:18|min:18'
            ],[
                'username.required' => '名称必填',
                'cardnum.required' => '身份证号必填',
                'username.min' => '名称不符合规范',
                'cardnum.max' => '身份证号格式不正确',
                'cardnum.min' => '身份证号格式不正确'
            ]);
            $user=new User();
            $user->name=$request['username'];
            $lastuser=User::orderBy('id','desc')->first();
            $lastid=intval($lastuser->account)+1;
            $user->account=sprintf("%04d",$lastid);
            $user->cardnum=$request['cardnum'];
            $user->password=bcrypt('123456');
            $user->sex=$request['sex'];
            $user->type = 3;
            $user->save();
            return redirect()->to(route('admin.user.userlist'))->with(['status'=>'添加成功']);
        }
        return view('admin/user/addgovuser');
    }
    public function addcomuser(Request $request){
        if ($request->isMethod('post')) {
            $this->validate($request, [
                'qyname' => 'required|min:3',
                'qycard' => 'required|min:10'
            ],[
                'qyname.required' => '企业名称必填',
                'qycard.required' => '营业执照编号必填',
                'qyname.min' => '企业名称不符合规范',
                'qycard.min' => '营业执照格式不正确'
            ]);
            $com=new Company();
            $com->comName=$request['qyname'];
            $com->type = 1;
            $com->schedule = 1;
            $com->licensenum=$request['qycard'];
            $com->save();

            $user=new User();
            $user->name=$request['qyname'];
            $lastuser=User::orderBy('id','desc')->first();
            $lastid=intval($lastuser->account)+1;
            $user->account=sprintf("%04d",$lastid);
            $user->password=bcrypt('123456');
            $user->type = 2;
            $user->cardnum=$request['qycard'];
            $user->companyId=$com->id;
            $user->save();

            $com->userId=$user->id;
            $com->save();
            return redirect()->to(route('admin.user.userlist'))->with(['status'=>'添加成功']);
        }
        return view('admin/user/addcomuser');
    }
    // 发送消息
    public function addmsg(Request $request){
        if ($request->isMethod('post')) {
            $this->validate($request, [
                'userids' => 'required',
                'title' => 'required',
                'content' => 'required'
            ],[
                'userid.required' => '接收人必填',
                'title.required' => '消息标题必填',
                'content.required' => '消息内容必填'
            ]);
            
            
            $msgtext=new MessageText();
            $msgtext->title=$request->input('title');
            $msgtext->content=$request->input('content');
            $msgtext->type=2;
            $msgtext->add_time=time();
            if($request->has('fileList') && $request->input('fileList') ){
                $file=$request->input('fileList')[0];
                $msgtext->attachment=$file['response']['filepath'];
                $msgtext->file_name=$file['name'];
            }
            $msgtext->save();

            foreach ($request->input('userids') as $userid) {
                $msg=new Message();
                $msg->from_id=Auth::guard('admin')->user()->id;
                $msg->to_id=$userid;
                $msg->message_id=$msgtext->id;
                $msg->status=0;
                $msg->save();
            }
            $data = [
                'code' => 1,
                'msg'   => '发送成功'
            ];
            return response()->json($data);
        }
        return view('admin/msg/sendmsg');
    }
    // 发送消息
    public function addmsg2(Request $request){
        
        return view('admin/msg/sendmsg2');
    }
    public function getmsgusers(){
        $users=User::orderBy('name','asc')->where('type',1)->select('id','name')->get();
        $data = [
            'code' => 0,
            'msg'   => '请求成功',
            'data'  => $users
        ];
        return response()->json($data);
    }
    // 获取分类下的用户
    public function gettypeusers(){
        $arr=[
            'all'=>[
                'id'=>31,
                'type'=>'user',
                'name'=>'全部',
                'users'=>[]
            ],
            'community_user'=>[
                'id'=>32,
                'type'=>'user',
                'name'=>'工作人员',
                'users'=>[]
            ],
            'community_admin'=>[
                'id'=>33,
                'type'=>'user',
                'name'=>'社区管理员',
                'users'=>[]
            ],
            
        ];
        $cates=Checkcategory::where('type','>',0)->orderBy('type','asc')->get();
        foreach ($cates as $k => $v) {
            $arr[$v->id]=[
                'id'=>$v->id,
                'type'=>'company',
                'name'=>$v->name,
                'users'=>[]
            ];
        }
        $users=User::where('is_delete',0)->get();
        foreach ($users as $k => $v) {
            $arr['all']['users'][]=$v;
            if($v->type==1 && $v->role=='community_user'){
                // 工作人员
                $arr['community_user']['users'][]=$v;
            }else if($v->type==1 && $v->role=='community_admin'){
                $arr['community_admin']['users'][]=$v;
            }else if($v->type>1){
                if($v->cominfo && $v->cominfo->categoryId>0){
                    $arr[$v->cominfo->categoryId]['users'][]=$v;
                }
            }
        }
        $data = [
            'code' => 1,
            'msg'   => '请求成功',
            'data'  => array_values($arr)
        ];
        return response()->json($data);
    }
    public function getmsgusers2(){
        $users=User::orderBy('name','asc')->where('type','>',1)->select('id','name')->get();
        $data = [
            'code' => 0,
            'msg'   => '请求成功',
            'data'  => $users
        ];
        return response()->json($data);
    }
    // 禁用用户
    public function delctl(Request $request){
        if(isset($request['id']) && isset($request['value'])){
            $user=User::find($request['id']);
            $user->is_delete=$request['value']=='true'?0:1;
            $user->save();
            $data=[
                'code'=>1,
                'msg'=>'设置成功'
            ];
        }else{
            $data=[
                'code'=>0,
                'msg'=>'设置失败'
            ];
        }
        return response()->json($data);
    }
    // 删除部门
    public function deldep(Request $request){
        $count=User::where('department_id',$request['id'])->count();
        if($count==0){
            $dep=Department::find($request['id']);
            $dep->delete();
            $data=[
                'code'=>1,
                'msg'=>'删除成功'
            ];
        }else{
            $data=[
                'code'=>0,
                'msg'=>'该部门尚有'.$count.'位成员，无法删除该部门'
            ];
        }
        return response()->json($data);
    }
    // 删除角色
    public function delrole(Request $request){
        $role=AdminUserRole::find($request['id']);
        $role->delete();
        $data=[
            'code'=>1,
            'msg'=>'删除成功'
        ];
        return response()->json($data);
    }
    // 删除岗位
    public function deljob(Request $request){
        $count=User::where('job_id',$request['id'])->count();
        if($count==0){
            $dep=Job::find($request['id']);
            $dep->delete();
            $data=[
                'code'=>1,
                'msg'=>'删除成功'
            ];
        }else{
            $data=[
                'code'=>0,
                'msg'=>'该岗位尚有'.$count.'位成员，无法删除该岗位'
            ];
        }
        return response()->json($data);
    }
    // 重置密码
    public function resetpwd(Request $request){
        if(isset($request['id'])){
            $user=User::find($request['id']);
            $user->password=bcrypt('BZzd@123456');
            $user->save();
            $data=[
                'code'=>1,
                'msg'=>'重置密码成功'
            ];
        }else{
            $data=[
                'code'=>0,
                'msg'=>'重置密码失败'
            ];
        }
        return response()->json($data);
    }
    public function getuserlistdata(Request $request){
        $query=User::with('job','department','place_3','place_4','place_5')->where('type',1)->orderBy('id','asc');
        if(isset($request['keyword']) && $request['keyword']){
            $places=Area::where('name','like','%'.$request['keyword'].'%')->pluck('id')->toArray();
            if($places){
                $query->where(function($q)use($request,$places){
                    $q->where('name','like','%'.$request['keyword'].'%')->orWhere('account','like','%'.$request['keyword'].'%')->orWhere('sex','like','%'.$request['keyword'].'%')->orWhereIn('province',$places)->orWhereIn('city',$places)->orWhereIn('district',$places)->orWhereIn('street',$places)->orWhereIn('community',$places);
                });
            }else{
                $query->where(function($q)use($request){
                    $q->where('name','like','%'.$request['keyword'].'%')->orWhere('account','like','%'.$request['keyword'].'%')->orWhere('sex','like','%'.$request['keyword'].'%');
                });
            }
        }
        $role=Auth::guard('admin')->user()->role;
        if(in_array($role,['sports','miit','business','travel','hygiene','emergency','policea','policeb','enforcement','supervise','rescue'])){
            $query->where('role',$role);
        }else if(in_array($role,['admin','district_admin','district_user'])){
            // 管理员 、 区级人员
        }else if(in_array($role,['street_admin','street_user'])){
            // 街道级人员
            $query->where('street',Auth::guard('admin')->user()->street)->whereIn('role',['community_admin','community_user','normal','street_admin','street_user']);
        }else if(in_array($role,['community_admin','community_user'])){
            // 社区级人员
            $query->where('community',Auth::guard('admin')->user()->community)->whereIn('role',['community_admin','community_user','normal']);
        }else{
            // 异常返回空
            $query->whereRaw('1=2');
        }

        
        $res=$query->paginate($request->get('limit',15))->toArray();
        $data = [
            'code' => 0,
            'msg'   => '请求成功',
            'count' => $res['total'],
            'data'  => $res['data']
        ];
        return response()->json($data);
    }
    public function getrolelistdata(Request $request){
        $res=AdminUserRole::paginate($request->get('limit',15))->toArray();
        $data = [
            'code' => 0,
            'msg'   => '请求成功',
            'count' => $res['total'],
            'data'  => $res['data']
        ];
        return response()->json($data);
    }
    public function getrolelistdata2(Request $request){
        $data = [
            'code' => 0,
            'msg'   => '请求成功',
            'count' => 3,
            'data'  => [
                ['id'=>1,'name'=>'安全员','note'=>''],
                ['id'=>2,'name'=>'库管员','note'=>''],
                ['id'=>3,'name'=>'考勤','note'=>''],
            ]
        ];
        return response()->json($data);
    }
    public function getdeplistdata(Request $request){
        $query=Department::with('users')->orderBy('id','asc');
        if(isset($request['keyword'])){
            $query->where('department','like','%'.$request['keyword'].'%')->orWhere('remark','like','%'.$request['keyword'].'%');
        }
        $res=$query->paginate($request->get('limit',15))->toArray();
        $data = [
            'code' => 0,
            'msg'   => '请求成功',
            'count' => $res['total'],
            'data'  => $res['data']
        ];
        return response()->json($data);
    }
    
    public function getjoblistdata(Request $request){
        $query=Job::with('users')->orderBy('id','asc');
        if(isset($request['keyword'])){
            $query->where('job','like','%'.$request['keyword'].'%')->orWhere('remark','like','%'.$request['keyword'].'%');
        }
        $res=$query->paginate($request->get('limit',15))->toArray();
        $data = [
            'code' => 0,
            'msg'   => '请求成功',
            'count' => $res['total'],
            'data'  => $res['data']
        ];
        return response()->json($data);
    }
    public function getmsglistdata(Request $request){
        $query=MessageText::with(['message.frominfo','message.toinfo'])->orderBy('id','desc');
        if(isset($request['keyword'])){
            $query->where('title','like','%'.$request['keyword'].'%')->orWhere('content','like','%'.$request['keyword'].'%');
        }
        if(isset($request['type'])){ //类型1：消息互通2：消息下发；3：随手拍指派提醒；4：出库提醒
            $query->where('type',$request['type']);
        }
        $res=$query->paginate($request->get('limit',15))->toArray();
        foreach ($res['data'] as $k => &$v) {
            $v['add_time']=date('Y-m-d H:i:s',$v['add_time']);
            $v['from_person']='';
            $v['to_person']='';
            $v['msgstate']='';
            foreach ($v['message'] as $m => $n) {
                $v['from_person']=$n['frominfo']['name'];
                $v['to_person'].=$n['toinfo']['name'].'；';
                if(intval($n['status'])==0){
                    // 消息未读
                    $v['msgstate'].=$n['toinfo']['name'].'、';
                }
            }
            $v['to_person']=trim($v['to_person'],'；');
            if($v['msgstate']==''){
                $v['msgstate']='已读';
            }else{
                $v['msgstate']=trim($v['msgstate'],'、').'未读';
            }
        }
        $data = [
            'code' => 0,
            'msg'   => '请求成功',
            'count' => $res['total'],
            'data'  => $res['data']
        ];
        return response()->json($data);
    }
    // public function getmsglistdata(Request $request){
    //     $query=MessageText::with(['message.frominfo','message.toinfo'])->orderBy('id','desc');
    //     if(isset($request['keyword'])){
    //         $query->where('title','like','%'.$request['keyword'].'%')->orWhere('content','like','%'.$request['keyword'].'%');
    //     }
    //     $res=$query->paginate($request->get('limit',15))->toArray();
    //     foreach ($res['data'] as $k => &$v) {
    //         $v['add_time']=date('Y-m-d H:i:s',$v['add_time']);
    //         $v['from_person']='';
    //         $v['to_person']='';
    //         $v['msgstate']='';
    //         foreach ($v['message'] as $m => $n) {
    //             $v['from_person']=$n['frominfo']['name'];
    //             $v['to_person'].=$n['toinfo']['name'].'；';
    //             if(intval($n['status'])==0){
    //                 // 消息未读
    //                 $v['msgstate'].=$n['toinfo']['name'].'、';
    //             }
    //         }
    //         $v['to_person']=trim($v['to_person'],'；');
    //         if($v['msgstate']==''){
    //             $v['msgstate']='已读';
    //         }else{
    //             $v['msgstate']=trim($v['msgstate'],'、').'未读';
    //         }
    //     }
    //     $data = [
    //         'code' => 0,
    //         'msg'   => '请求成功',
    //         'count' => $res['total'],
    //         'data'  => $res['data']
    //     ];
    //     return response()->json($data);
    // }
    public function getresident(Request $request){
        $userid=$request->input('id');
        $res=User::with('cominfo','resident')->where('id',$userid)->first();
        $res->resident->live_info=json_decode($res->resident->live_info);
        $res->resident->farm_info=json_decode($res->resident->farm_info);
        $res->resident->car_info=json_decode($res->resident->car_info);
        return response()->json(['data'=>$res,'message'=>'请求成功','code' => 200], 200);
    }
    public function chgresident(Request $request){
        $data = $request->all();
        $rules = [
            'user_id'=>'required',
            'leader' => 'required|max:240',
            'province' => 'required',
            'city' => 'required',
            'district' => 'required',
            'street' => 'required',
            'address' =>'required|max:100',
            'idcard'=>'required',
            'door_code'=>'required',
            'birthday'=>'required',
            'live_people'=>'required',
            'live_type'=>'required',
            'old_type'=>'required',
            'fire_type'=>'required',
            'farm_num'=>'required'
        ];
        $message = [
            'user_id.required' => '用户id不能为空',
            'leader.required' => '户主名称不能为空',
            'leader.max' => '户主名称长度不能超过240个字符',
            'province.required' => '请选择省份',
            'city.required' => '请选择城市',
            'district.required' => '请选择区',
            'street.required' => '请选择街道',
            'address.required' => '请输入街道、门牌号等信息',
            'address.max' => '街道、门牌号等信息长度不能超过100个字符',
            'idcard.required'=>'身份证号不能为空',
            'door_code.required'=>'门牌号不能为空',
            'birthday.required'=>'出生年月不能为空',
            'live_people.required'=>'居住人数不能为空',
            'live_type.required'=>'住户类型不能为空',
            'old_type.required'=>'老人类型不能为空',
            'fire_type.required'=>'燃气类型不能为空',
            'farm_num.required'=>'农田数不能为空',
        ];
        $validator = Validator::make($data,$rules,$message);
        if ($validator->fails()) {
            $error = $validator->errors()->first();
            return redirect(route('admin.company.residentlist1'))->withErrors(['status'=>$error]);
        }

        DB::beginTransaction();
        try {
            $user_id=$request->input('user_id');
            $com_id=User::where('id',$user_id)->first()->companyId;
            $company=[
                'comName' => $request->input("leader"),
                'leader' => $request->input("leader"),
                'province' => $request->input("province"),
                'city' => $request->input("city"),
                'district' => $request->input("district"),
                'street' => $request->input("street"),
                'community' => $request->input("community"),
                'address' => $request->input("address"),
                'area'=>$request->input("live_area"),
                'idcard' => $request->input("idcard")
            ];
            Company::where('id', $com_id)->update($company);

            $comuser=[
                'name'=>$company['leader'],
                'account'=>$company['idcard'],
                'province'=>$company['province'],
                'city'=> $company['city'],
                'district'=>$company['district'] ,
                'street'=>$company['street'] ,
                'community'=>$company['community']
            ];
            User::where('id', $user_id)->update($comuser);
            $live_info=[];
            if(isset($data['live_info_1']) && is_array($data['live_info_1'])){
                foreach ($data['live_info_1'] as $k => $v) {
                    if($v && isset($data['live_info_2'][$k]) && isset($data['live_info_3'][$k])){
                        $temp_live_info=[
                            'live_info_1'=>$v,
                            'live_info_2'=>$data['live_info_2'][$k],
                            'live_info_3'=>$data['live_info_3'][$k]
                        ];
                        $live_info[]=$temp_live_info;
                    }
                }
            }
            $live_info=json_encode($live_info);

            $car_info=[];
            if(isset($data['car_info_1']) && is_array($data['car_info_1'])){
                foreach ($data['car_info_1'] as $k => $v) {
                    if($v && isset($data['car_info_2'][$k]) && isset($data['car_info_3'][$k])){
                        $temp_car_info=[
                            'car_info_1'=>$v,
                            'car_info_2'=>$data['car_info_2'][$k],
                            'car_info_3'=>$data['car_info_3'][$k]
                        ];
                        $car_info[]=$temp_car_info;
                    }
                }
            }
                
            $car_info=json_encode($car_info);

            $farm_info=[];
            if(isset($data['farm_info_1']) && is_array($data['farm_info_1'])){
                foreach ($data['farm_info_1'] as $k => $v) {
                    if($v && isset($data['farm_info_2'][$k])){
                        $temp_farm_info=[
                            'farm_info_1'=>$v,
                            'farm_info_2'=>$data['farm_info_2'][$k]
                        ];
                        $farm_info[]=$temp_farm_info;
                    }
                }
            }
                
            $farm_info=json_encode($farm_info);
            $resident=[
                'door_code'=>$request->input('door_code'),
                'birthday'=>$request->input('birthday'),
                'live_area'=>$request->input('live_area')??null,
                'live_people'=>$request->input('live_people'),
                'live_info'=>$live_info,
                'live_type'=>$request->input('live_type'),
                'warm_type'=>$request->input('warm_type'),
                'alarm_type'=>$request->input('alarm_type'),
                'old_type'=>$request->input('old_type'),
                'fire_type'=>$request->input('fire_type'),
                'fire5_num1'=>$request->input('fire5_num1')??null,
                'fire5_num2'=>$request->input('fire5_num2')??null,
                'fire15_num1'=>$request->input('fire15_num1')??null,
                'fire15_num2'=>$request->input('fire15_num2')??null,
                'fire50_num1'=>$request->input('fire50_num1')??null,
                'fire50_num2'=>$request->input('fire50_num2')??null,
                'farm_num'=>$request->input('farm_num'),
                'farm_info'=>$farm_info,
                'car_info'=>$car_info,
                'note'=>$request->input('note')??''
            ];
            $resident_obj=Resident::where('user_id', $user_id)->first();
            if($resident_obj){
                Resident::where('user_id', $user_id)->update($resident);
            }else{
                $resident['user_id']=$user_id;
                $resident['com_id']=$com_id;
                Resident::insert($resident);
            }
            DB::commit(); 
            return redirect()->to(route('admin.company.residentlist1'))->with(['status'=>'修改成功']);
        } catch(Exception $e) {
            DB::rollBack();
            return redirect(route('admin.company.residentlist1'))->withErrors(['status'=>$e->getMessage()]);
        }
    }
    public function comlistdata(Request $request){
        $community=$request->input('community','');
        
        $user=Auth::guard('admin')->user();
        if($user->type==1){
            $query=Company::with('commanager','safeOfficer','place_1','place_2','place_3','place_4','place_5','place_11','place_22','place_33','place_44','place_55','tagrelations.taginfo')->orderBy('id','asc');
        }else{
            $query=Company::with('commanager','safeOfficer','place_1','place_2','place_3','place_4','place_5','place_11','place_22','place_33','place_44','place_55','tagrelations.taginfo')->orderBy('id','asc')->where('userId',$user->id);
        }
        if($community){
            $query->where('community',$community);
        }
        $query->where('enterprise','<>',3);
        if(isset($request['keyword'])){
            $query->where('comName','like','%'.$request['keyword'].'%');
        }
        $res=$query->paginate($request->get('limit',8))->toArray();
        $data = [
            'code' => 0,
            'msg'   => '请求成功',
            'count' => $res['total'],
            'data'  => $res['data']
        ];
        return response()->json($data);
    }
    public function residentlistdata(Request $request){
        $community=$request->input('community','');
        $user=Auth::guard('admin')->user();
        if($user->type==1){
            $query=Company::with('place_1','place_2','place_3','place_4','place_5','resident','userinfo')->orderBy('id','asc');
            
        }else{
            $query=Company::with('place_1','place_2','place_3','place_4','place_5','resident','userinfo')->orderBy('id','asc')->where('userId',$user->id);
        }
        if($community){
            $query->where('community',$community);
        }
        $query->where('enterprise',3);
        if(isset($request['keyword'])){
            $query->where('comName','like','%'.$request['keyword'].'%');
        }
        $res=$query->paginate($request->get('limit',8))->toArray();
        $data = [
            'code' => 0,
            'msg'   => '请求成功',
            'count' => $res['total'],
            'data'  => $res['data']
        ];
        return response()->json($data);
    }
    // 修改公司信息
    public function comedit(Request $req){
        $com=Company::with('commanager','safeOfficer')->where('id',$req['id'])->first();
        // $provinces=Xtprovince::select('PROVINCE_CODE','PROVINCE_NAME')->get()->toArray();
        $provinces=Area::where('level',1)->select('id','name')->get();
        // 经营地址
        if($com->province){
            $citys=Area::where('level',2)->where('pid',$com->province)->select('id','name')->get();
        }else{
            $citys=[];
        }
        if($com->city){
            $districts=Area::where('level',3)->where('pid',$com->city)->select('id','name')->get();
        }else{
            $districts=[];
        }
        if($com->district){
            $streets=Area::where('level',4)->where('pid',$com->district)->select('id','name')->get();
        }else{
            $streets=[];
        }
        if($com->street){
            $communitys=Area::where('level',5)->where('pid',$com->street)->select('id','name')->get();
        }else{
            $communitys=[];
        }

        // 注册地址
        $provinces1=$provinces;
        if($com->province1){
            $citys1=Area::where('level',2)->where('pid',$com->province1)->select('id','name')->get();
        }else{
            $citys1=[];
        }
        if($com->city1){
            $districts1=Area::where('level',3)->where('pid',$com->city1)->select('id','name')->get();
        }else{
            $districts1=[];
        }
        if($com->district1){
            $streets1=Area::where('level',4)->where('pid',$com->district1)->select('id','name')->get();
        }else{
            $streets1=[];
        }
        if($com->street1){
            $communitys1=Area::where('level',5)->where('pid',$com->street1)->select('id','name')->get();
        }else{
            $communitys1=[];
        }
        $tags=Tag::get();
        $tagrelations=TagRelation::where('com_id',$com->id)->pluck('tag_id')->toArray();
        return view('admin/com/comedit',compact('com','provinces','citys','districts','streets','communitys','provinces1','citys1','districts1','streets1','communitys1','tags','tagrelations'));
    }
    // 修改住户信息
    public function residentedit(Request $req){
        $com=Company::with('place_1','place_2','place_3','place_4','place_5','resident','userinfo')->where('id',$req['id'])->first();
        if($com->resident){
            $live_info=json_decode($com->resident->live_info);
            $farm_info=json_decode($com->resident->farm_info);
            $car_info=json_decode($com->resident->car_info);
        }else{
            $live_info=[];
            $farm_info=[];
            $car_info=[];
        }

        $provinces=Area::where('level',1)->select('id','name')->get();
        if($com->province){
            $citys=Area::where('level',2)->where('pid',$com->province)->select('id','name')->get();
        }else{
            $citys=[];
        }
        if($com->city){
            $districts=Area::where('level',3)->where('pid',$com->city)->select('id','name')->get();
        }else{
            $districts=[];
        }
        if($com->district){
            $streets=Area::where('level',4)->where('pid',$com->district)->select('id','name')->get();
        }else{
            $streets=[];
        }
        if($com->street){
            $communitys=Area::where('level',5)->where('pid',$com->street)->select('id','name')->get();
        }else{
            $communitys=[];
        }
        // dd($com->toArray());
        // $com=$com->toArray();
        // dd(compact('com','provinces','citys','districts','streets','communitys','live_info','farm_info','car_info'));
        return view('admin/com/residentedit',compact('com','provinces','citys','districts','streets','communitys','live_info','farm_info','car_info'));
    }
    //  获取省市区街道 社区 数组
    public function getareajson(Request $req){
        $level=$req['lvl'];
        if($level==1){
            $provinces=Area::where('level',1)->select('id','name')->get()->toArray();
            $data=[
                'code'=>0,
                'msg'=>'请求成功',
                'data'=>$provinces
            ];
            return response()->json($data);
        }else{
            $code=$req['code'];
            $json=Area::where('level',$level)->where('pid',$code)->select('id','name')->get()->toArray();
            $data=[
                'code'=>0,
                'msg'=>'请求成功',
                'data'=>$json
            ];
            return response()->json($data);
        }
    }
    public function getareajson2(Request $req){
        $level=$req['lvl'];
        if($level==1){
            $provinces=Xtprovince::get()->toArray();
            $data=[
                'code'=>0,
                'msg'=>'请求成功',
                'data'=>$provinces
            ];
            return response()->json($data);
        }elseif($level==2){
            $province_code=$req['province_code'];
            $citys=Xtcity::where('PROVINCE_CODE',$province_code)->get()->toArray();
            $data=[
                'code'=>0,
                'msg'=>'请求成功',
                'data'=>$citys
            ];
            return response()->json($data);
        }elseif($level==3){
            $city_code=$req['city_code'];
            $citys=Xttown::where('CITY_CODE',$city_code)->get()->toArray();
            $data=[
                'code'=>0,
                'msg'=>'请求成功',
                'data'=>$citys
            ];
            return response()->json($data);
        }elseif($level==4){
            $town_code=$req['town_code'];
            $towns=Xtstreet::where('AREA_CODE',$town_code)->get()->toArray();
            $data=[
                'code'=>0,
                'msg'=>'请求成功',
                'data'=>$towns
            ];
            return response()->json($data);
        }else{
            $data=[
                'code'=>1,
                'msg'=>'请求失败'
            ];
            return response()->json($data);
        }
        
    }
    /**
     * @param Request $quest
     * @return mixed
     */
    public function reportlistcontent(Request $quest)
    {
        $cityname = '青岛市';
        $area_first = array();
        $area_first = Area::where('name','青岛市')->first()->toArray();
        $parent_path = $area_first['node'].','.$area_first['id'];
        $where = array();
        $where[] = array('node', 'like', $parent_path.'%');
        $area = Area::where($where)->select(array('id','pid','node','name','level'))->get()->toArray();
        $tree = $this->getProvinceTree($area_first, $area);
        $categoryId = $quest->input('categoryId');
        $areaid = [];
        if (!empty($quest->input('city'))){
            $pid = $quest->input('city');
            $level = $quest->input('level');
            switch ($level) {
                case 1:
                    $diqu = 'district';
                    break;
                case 2:
                    $diqu = 'street';
                    break;
                case 3:
                    $diqu = 'community';
                    break;
            }
        }else{
            $pid = 370212;
            $diqu = 'street';
            $level = 2;
        }
        if ($level == 4){
            $area = DB::table('area')->where('id','=',$pid)->select(['id','name'])->get();
        }else{
            $area = DB::table('area')->where('pid','=',$pid)->select(['id','name'])->get();
        }
        $cityname = Area::find($pid)->name;
        foreach ($area as $item){
            array_push($areaid,$item->id);
        }
        $name = !empty($quest->input('name'))?$quest->input('name'):'';
        if (!empty($categoryId)){
            if ($level == 4){
                $companyCom = Company::leftJoin('comcheck','company.id','comcheck.companyId')->select('company.*','comcheck.id as checkId','comcheck.created')->where('company.comName','like','%'.$name.'%')->where(['company.categoryId'=>$categoryId])->whereIn('company.community',$areaid);
            }else{
                $companyCom = Company::leftJoin('comcheck','company.id','comcheck.companyId')->select('company.*','comcheck.id as checkId','comcheck.created')->where('company.comName','like','%'.$name.'%')->where(['company.categoryId'=>$categoryId])->whereIn('company.'.$diqu,$areaid);
            }
        }else{
            if ($level == 4){
                $companyCom = Company::leftJoin('comcheck','company.id','comcheck.companyId')->select('company.*','comcheck.id as checkId','comcheck.created')->where('company.comName','like','%'.$name.'%')->whereIn('company.community',$areaid);
            }else{
                $companyCom = Company::leftJoin('comcheck','company.id','comcheck.companyId')->select('company.*','comcheck.id as checkId','comcheck.created')->where('company.comName','like','%'.$name.'%')->whereIn('company.'.$diqu,$areaid);
            }
        }
        $categorys = Checkcategory::all();
        $companys = $companyCom->paginate(8);
        $areaid = [];
        foreach ($companys as $k=>$v){
            (in_array($v->province,$areaid)||(empty($v->province)))?'':array_push($areaid,$v->province);
            (in_array($v->city,$areaid)||(empty($v->city)))?'':array_push($areaid,$v->city);
            (in_array($v->district,$areaid)||(empty($v->district)))?'':array_push($areaid,$v->district);
            (in_array($v->street,$areaid)||(empty($v->street)))?'':array_push($areaid,$v->street);
            (in_array($v->community,$areaid)||(empty($v->community)))?'':array_push($areaid,$v->community);
        }
        $area = Area::whereIn('id',$areaid)->get();
        $checkId = [];
        foreach($companys as $com){
            array_push($checkId,$com->checkId);
        }
        $comcheckdetail = Comcheckdetail::whereIn('checkId',$checkId)->get();
        return view('admin/work/reportlistcontent')
            ->with('companys',$companys)
            ->with('tree',$tree)
            ->with('comcheckdetail',$comcheckdetail)
            ->with('name',$name)
            ->with('cityname',$cityname)
            ->with('categoryId',$categoryId)
            ->with('area',$area)
            ->with('categorys',$categorys);
    }

    /**
     * @param Request $quest
     * @return mixed
     */
    public function reportone(Request $quest){
        $cityname = '青岛市';
        $area_first = array();
        $area_first = Area::where('name','青岛市')->first()->toArray();
        $parent_path = $area_first['node'].','.$area_first['id'];
        $where = array();
        $where[] = array('node', 'like', $parent_path.'%');
        $area = Area::where($where)->select(array('id','pid','node','name','level'))->get()->toArray();
        $tree = $this->getProvinceTree($area_first, $area);
        $companyId = $quest->input('id');
        $company =  Company::find($companyId);
        $comcheck = Comcheck::leftJoin('users','comcheck.userId','users.id')->select('comcheck.*','users.name')->where(['comcheck.companyId' => $companyId])->get();
        if (empty(json_decode(json_encode($comcheck), true))) {
            echo "<script>alert('该公司还未检查');history.go(-1);</script>";
            exit();
        }
        $comcheckdetail = Comcheckdetail::where(['checkId'=>$comcheck[0]->id])->where(['status'=>2])->get();
        $comcheckdetail = json_decode(json_encode($comcheckdetail), true);
        $is = empty($comcheckdetail)?1:0;
        if (!empty($comcheckdetail)) {        //有异常需要整改
            //获取异常点
            $negatives = [];
            foreach ($comcheckdetail as $k=>$v){
                if (!empty($v['negatives'])){
                    $arr = explode(',',$v['negatives']);
                    foreach ($arr as $ar){
                        if (!empty($ar)){
                            array_push($negatives,$ar);
                        }
                    }
                }
            }
            $negatives = Checknegative::whereIn('id',$negatives)->get();
            $comcheckagain = Comcheckagain::where(['checkId'=>$comcheck[0]->id])->get();
            if (!empty(json_decode(json_encode($comcheckagain), true))){
                $comcheckagainId = $comcheckagain[0]->id;
                $comcheckagaindetail = Comcheckagaindetail::where(['checkId'=>$comcheckagainId])->where(['status'=>1])->get();
                $comcheckagaindetail = json_decode(json_encode($comcheckagaindetail), true);
                $again = empty($comcheckagaindetail)?1:0;
            }else{
                $again = 1;
            }
        }else{
            $again = 1;         //不存在隐患不需要整改整改状态正常
            $negatives = '';
        }
        $negatives = json_decode(json_encode($negatives), true);
        $again = empty($comcheckdetail)?1:0;
        $categoryname = empty($company->categoryId)?'':Checkcategory::find($company->categoryId)->name;
        $address = (empty($company->province)?'':(Area::find($company->province)->name)).(empty($company->city)?'':(Area::find($company->city)->name)).(empty($company->district)?'':(Area::find($company->district)->name)).(empty($company->street)?'':(Area::find($company->street)->name)).(empty($company->community)?'':(Area::find($company->community)->name)).$company->address;
        $shequ = empty($company->community)?'':Area::find($company->community)->name;
        return view('admin/work/reportone')
            ->with('comcheck',$comcheck)
            ->with('tree',$tree)
            ->with('address',$address)
            ->with('categoryname',$categoryname)
            ->with('shequ',$shequ)
            ->with('negatives',$negatives)
            ->with('again',$again)
            ->with('companyId',$companyId)
            ->with('is',$is)
            ->with('company',$company);
    }

    /**
     * @param Request $quest
     * @return mixed
     */
    public function reportones(Request $quest){
        $cityname = '青岛市';
        $area_first = array();
        $area_first = Area::where('name','青岛市')->first()->toArray();
        $parent_path = $area_first['node'].','.$area_first['id'];
        $where = array();
        $where[] = array('node', 'like', $parent_path.'%');
        $area = Area::where($where)->select(array('id','pid','node','name','level'))->get()->toArray();
        $tree = $this->getProvinceTree($area_first, $area);
        $companyId = $quest->input('id');
        $company =  Company::find($companyId);
        $comcheck = Comcheck::leftJoin('users','comcheck.userId','users.id')->select('comcheck.*','users.name')->where(['comcheck.companyId' => $companyId])->get();
        if (empty(json_decode(json_encode($comcheck), true))) {
            echo "<script>alert('该公司还未检查');history.go(-1);</script>";
            exit();
        }
            $comcheckdetail = Comcheckdetail::where(['checkId'=>$comcheck[0]->id])->where(['status'=>2])->get();
        $comcheckdetail = json_decode(json_encode($comcheckdetail), true);
        $is = empty($comcheckdetail)?1:0;
        if (!empty($comcheckdetail)) {        //有异常需要整改
            //获取异常点
            $negatives = [];
            foreach ($comcheckdetail as $k=>$v){
                if (!empty($v['negatives'])){
                    $arr = explode(',',$v['negatives']);
                    foreach ($arr as $ar){
                        if (!empty($ar)){
                            array_push($negatives,$ar);
                        }
                    }
                }
            }
            $negatives = Checknegative::whereIn('id',$negatives)->get();
            $comcheckagain = Comcheckagain::where(['checkId'=>$comcheck[0]->id])->get();
            if (!empty(json_decode(json_encode($comcheckagain), true))){
                $comcheckagainId = $comcheckagain[0]->id;
                $comcheckagaindetail = Comcheckagaindetail::where(['checkId'=>$comcheckagainId])->where(['status'=>1])->get();
                $comcheckagaindetail = json_decode(json_encode($comcheckagaindetail), true);
                $again = empty($comcheckagaindetail)?1:0;
            }else{
                $again = 1;
            }
        }else{
            $again = 1;         //不存在隐患不需要整改整改状态正常
            $negatives = '';
        }
        $negatives = json_decode(json_encode($negatives), true);
        $categoryname = empty($company->categoryId)?'':Checkcategory::find($company->categoryId)->name;
        $address = (empty($company->province)?'':(Area::find($company->province)->name)).(empty($company->city)?'':(Area::find($company->city)->name)).(empty($company->district)?'':(Area::find($company->district)->name)).(empty($company->street)?'':(Area::find($company->street)->name)).(empty($company->community)?'':(Area::find($company->community)->name)).$company->address;
        $shequ = empty($company->community)?'':Area::find($company->community)->name;

        return view('admin/work/reportones')
            ->with('comcheck',$comcheck)
            ->with('tree',$tree)
            ->with('address',$address)
            ->with('categoryname',$categoryname)
            ->with('shequ',$shequ)
            ->with('companyId',$companyId)
            ->with('negatives',$negatives)
            ->with('again',$again)
            ->with('is',$is)
            ->with('company',$company);
    }

    /**
     * @param Request $quest
     * @throws \PHPExcel_Exception
     * @throws \PHPExcel_Writer_Exception
     */
    public function reportexport(Request $quest){
        $categoryId = $quest->input('categoryId');
        $areaid = [];
        if (!empty($quest->input('city'))){
            $pid = $quest->input('city');
            $level = $quest->input('level');
            switch ($level) {
                case 1:
                    $diqu = 'district';
                    break;
                case 2:
                    $diqu = 'street';
                    break;
                case 3:
                    $diqu = 'community';
                    break;
            }
        }else{
            $pid = 370212;
            $diqu = 'street';
            $level = 2;
        }
        if ($level == 4){
            $area = DB::table('area')->where('id','=',$pid)->select(['id','name'])->get();
        }else{
            $area = DB::table('area')->where('pid','=',$pid)->select(['id','name'])->get();
        }
        $cityname = Area::find($pid)->name;
        foreach ($area as $item){
            array_push($areaid,$item->id);
        }
        $name = !empty($quest->input('name'))?$quest->input('name'):'';
        if (!empty($categoryId)){
            if ($level == 4){
                $companyCom = Company::leftJoin('comcheck','company.id','comcheck.companyId')->select('company.*','comcheck.id as checkId','comcheck.created','comcheck.userId')->where('company.comName','like','%'.$name.'%')->where(['company.categoryId'=>$categoryId])->whereIn('company.community',$areaid);
            }else{
                $companyCom = Company::leftJoin('comcheck','company.id','comcheck.companyId')->select('company.*','comcheck.id as checkId','comcheck.created','comcheck.userId')->where('company.comName','like','%'.$name.'%')->where(['company.categoryId'=>$categoryId])->whereIn('company.'.$diqu,$areaid);
            }
        }else{
            if ($level == 4){
                $companyCom = Company::leftJoin('comcheck','company.id','comcheck.companyId')->select('company.*','comcheck.id as checkId','comcheck.created','comcheck.userId')->where('company.comName','like','%'.$name.'%')->whereIn('company.community',$areaid);
            }else{
                $companyCom = Company::leftJoin('comcheck','company.id','comcheck.companyId')->select('company.*','comcheck.id as checkId','comcheck.created','comcheck.userId')->where('company.comName','like','%'.$name.'%')->whereIn('company.'.$diqu,$areaid);
            }
        }
        $categorys = Checkcategory::all();
        $companys = $companyCom->get();
        $areaid = [];
        foreach ($companys as $k=>$v){
            (in_array($v->province,$areaid)||(empty($v->province)))?'':array_push($areaid,$v->province);
            (in_array($v->city,$areaid)||(empty($v->city)))?'':array_push($areaid,$v->city);
            (in_array($v->district,$areaid)||(empty($v->district)))?'':array_push($areaid,$v->district);
            (in_array($v->street,$areaid)||(empty($v->street)))?'':array_push($areaid,$v->street);
            (in_array($v->community,$areaid)||(empty($v->community)))?'':array_push($areaid,$v->community);
        }
        $area = Area::whereIn('id',$areaid)->get();
        $checkId = [];
        foreach($companys as $com){
            array_push($checkId,$com->checkId);
        }
        $comcheckdetail = Comcheckdetail::whereIn('checkId',$checkId)->where('status','=',2)->get();
        $comcheckagain = Comcheckagain::whereIn('checkId',$checkId)->get();
        //开始写入
        $excel = new \PHPExcel();
        $excel->getDefaultStyle()->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
        $objActSheet = $excel->getActiveSheet();
        $letter = array('A','B','C','D','E','F','G','H','I','J','K','L','M');
        $objActSheet->setCellValue("A1",$cityname."检查企业档案统计表");
        $objActSheet->mergeCells('A1:M1');
        $objActSheet->getStyle('A1')->getAlignment()->setWrapText(true);
        $objActSheet->getStyle('A1')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);//左右居中
        $objActSheet->getStyle('A1')->getFont()->setName('黑体');
        $objActSheet->getStyle('A1')->getFont()->setSize(20);
        $objActSheet->getStyle('A1')->getFont()->setBold(true);
        //part1
        $objActSheet->setCellValue("A2","单位名称");
        $objActSheet->setCellValue("B2","单位地址");
        $objActSheet->setCellValue("C2","企业类型");
        $objActSheet->setCellValue("D2","单位负责人");
        $objActSheet->setCellValue("E2","联系电话");
        $objActSheet->setCellValue("F2","单位人数");
        $objActSheet->setCellValue("G2","占地面积");
        $objActSheet->setCellValue("H2","检查时间");
        $objActSheet->setCellValue("I2","检查人员");
        $objActSheet->setCellValue("J2","隐患数量");
        $objActSheet->setCellValue("K2","复查时间");
        $objActSheet->setCellValue("L2","复查人员");
        $objActSheet->setCellValue("M2","备注");
        $orirow = 3;
        foreach ($companys as $company){
            $objActSheet->setCellValue("A".$orirow,$company->comName);
            $address = '';
            foreach ($area as $a){
                if ( !empty($company->district)&&$company->district == $a->id){
                    $address .= $a->name;
                }
                if ( !empty($company->street)&&$company->street == $a->id){
                    $address .= $a->name;
                }
                if ( !empty($company->community)&&$company->community == $a->id){
                    $address .= $a->name;
                }
            }
            $address .= $company->address;
            $objActSheet->setCellValue("B".$orirow,$address);

            foreach($categorys as $category){
                if (!empty($company->categoryId) && ($company->categoryId == $category->id)){
                    $objActSheet->setCellValue("C".$orirow,$category->name);
                }
            }
            $objActSheet->setCellValue("D".$orirow,$company->leader);
            $objActSheet->setCellValue("E".$orirow,$company->leaderPhone);
            $objActSheet->setCellValue("F".$orirow,$company->workersNum);
            $objActSheet->setCellValue("G".$orirow,$company->space);
            $objActSheet->setCellValue("H".$orirow,$company->created);
            if (!empty($company->userId)){
                $objActSheet->setCellValue("I".$orirow,User::find($company->userId)->name);
            }
            $checknum = 0;
            foreach ($comcheckdetail as $detail){
                if ($detail->checkId == $company->checkId){
                    $checknum += 1;
                }
            }
            $objActSheet->setCellValue("J".$orirow,$checknum);
            $againtime = '';
            $againuser = '';
            foreach ($comcheckagain as $again){
                if ($again->checkId == $company->checkId){
                    $againtime = date('Y-m-d',$again->checkTime);
                    $againuser = User::find($again->userId)->name;
                }
            }
            $objActSheet->setCellValue("K".$orirow,$againtime);
            $objActSheet->setCellValue("L".$orirow,$againuser);
            $objActSheet->setCellValue("M".$orirow,'');

            $orirow++;
        }
        //统一设置填充内容左右居中、字号、宽度
        foreach ($letter as $l){
            for ($x = 2;$x<=$orirow;$x++){
                $objActSheet->getStyle($l.$x)->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);//左右居中
                $objActSheet->getStyle($l.$x)->getFont()->setSize(10);
            }

        }
        for ($x=1; $x<=($orirow+10); $x++) {
            $objActSheet->getRowDimension($x)->setRowHeight(20);//统一设置行高度

        }
        $styleArray = array(
            'borders' => array(
                'allborders' => array(
                    'style' => \PHPExcel_Style_Border::BORDER_THIN,//细边框
                ),
            ),
        );
        $objActSheet->getColumnDimension('A')->setWidth(30);
        $objActSheet->getColumnDimension('B')->setWidth(60);
        $objActSheet->getColumnDimension('E')->setWidth(20);
        $objActSheet->getColumnDimension('H')->setWidth(30);
        $objActSheet->getColumnDimension('K')->setWidth(30);

        $objActSheet->getStyle('A1:M'.($orirow+10))->applyFromArray($styleArray);//设置粗边框
        $objActSheet->getRowDimension(1)->setRowHeight(30);//单独设置行高度
        $write = new \PHPExcel_Writer_Excel5($excel);
        ob_end_clean();
        header("Pragma: public");
        header("Expires: 0");
        header("Cache-Control:must-revalidate, post-check=0, pre-check=0");
        header("Content-Type:application/force-download");
        header("Content-Type:application/vnd.ms-execl");
        header("Content-Type:application/octet-stream");
        header("Content-Type:application/download");;
        header('Content-Disposition:attachment;filename="'.$cityname.''."检查企业档案统计表".'.xls"');
        header("Content-Transfer-Encoding:binary");
        $write->save('php://output');
        exit();
    }

    /**
     * @param Request $request
     * @return \Illuminate\Contracts\View\Factory|\Illuminate\View\View
     */
    public function chart(Request $request){
        $cityname = '崂山区';
        $area_first = array();
        $area_first = Area::where('name','青岛市')->first()->toArray();
        $parent_path = $area_first['node'].','.$area_first['id'];
        $where = array();
        $where[] = array('node', 'like', $parent_path.'%');
        $area = Area::where($where)->select(array('id','pid','node','name','level'))->get()->toArray();
        $tree = $this->getProvinceTree($area_first, $area);

        return view('admin/work/chart')
            ->with('cityname',$cityname)
            ->with('tree',$tree);
    }

    /**
     * @param Request $request
     * @return mixed
     */
    public function chartcontent(Request $request){
        $area_first = array();
        $area_first = Area::where('name','青岛市')->first()->toArray();
        $parent_path = $area_first['node'].','.$area_first['id'];
        $where = array();
        $where[] = array('node', 'like', $parent_path.'%');
        $area = Area::where($where)->select(array('id','pid','node','name','level'))->get()->toArray();
        $tree = $this->getProvinceTree($area_first, $area);
        $category = DB::table('checkcategory')->select(['id','name'])->get();
        $cateArray = [];
        foreach($category as $k => $v)
        {
            $cateArray[] = $v->name;
        }
        array_push($cateArray,'其它');
        $areaid = [];
        if (!empty($request->input('city'))){
            $pid = $request->input('city');
            $level = $request->input('level');
            switch ($level) {
                case 1:
                    $diqu = 'district';
                    break;
                case 2:
                    $diqu = 'street';
                    break;
                case 3:
                    $diqu = 'community';
                    break;
            }
        }else{
            $pid = 370212;
            $diqu = 'street';
            $level = 2;
        }
        if ($level == 4){
            $area = DB::table('area')->where('id','=',$pid)->select(['id','name'])->get();
        }else{
            $area = DB::table('area')->where('pid','=',$pid)->select(['id','name'])->get();
        }
        $cityname = Area::find($pid)->name;
        foreach ($area as $item){
            array_push($areaid,$item->id);
        }
        if ($level == 4){
            $data  = Company::leftJoin('checkcategory','company.categoryId','checkcategory.id')->leftJoin('area','company.community','area.id')->select('company.id','checkcategory.id as categoryId','area.name')->whereIn('company.community',$areaid)->orderBy('company.id','desc')->get();

        }else{
            $data  = Company::leftJoin('checkcategory','company.categoryId','checkcategory.id')->leftJoin('area','company.'.$diqu,'area.id')->select('company.id','checkcategory.id as categoryId','area.name')->whereIn('company.'.$diqu,$areaid)->orderBy('company.id','desc')->get();

        }
        $data = json_decode(json_encode($data, true));
        $dataarr = [];
        foreach ($area as $item){
            $arr = [];
            $a = 0;$b=0;$c=0;$d=0;$e=0;$f=0;$g=0;$h=0;$i=0;$j=0;$k=0;
            foreach ($data as $da){
                if ($da->name == $item->name){
                    $da->categoryId == 1?$a+=1:'';
                    $da->categoryId == 2?$b+=1:'';
                    $da->categoryId == 3?$c+=1:'';
                    $da->categoryId == 4?$d+=1:'';
                    $da->categoryId == 5?$e+=1:'';
                    $da->categoryId == 6?$f+=1:'';
                    $da->categoryId == 7?$g+=1:'';
                    $da->categoryId == 8?$h+=1:'';
                    $da->categoryId == 9?$i+=1:'';
                    $da->categoryId == 10?$j+=1:'';
                    $da->categoryId >=11?$k+=1:'';
                }
            }
            $arr = ['product'=>$item->name,'小餐饮'=>$a,'小旅馆'=>$b,'小商场'=>$c,'公共娱乐'=>$d,'小医疗场所'=>$e,'休闲健身'=>$f,'小学校'=>$g,'烟花爆竹'=>$h,'4s店'=>$i,'小企业'=>$j,'其它'=>$k,];
            array_push($dataarr,$arr);
        }
        $cateCount = DB::select("select count(company.id) as count,checkcategory.name from checkcategory
								 left join company on checkcategory.id = company.category group by checkcategory.id ");
        array_unshift($cateArray,'product');
//        dd(json_encode($cateArray,JSON_UNESCAPED_UNICODE));
        return view('admin/work/chartcontent')
            ->with('cate',json_encode($cateArray,JSON_UNESCAPED_UNICODE))
            ->with('category',$category)
            ->with('cityname',$cityname)
            ->with('tree',$tree)
            ->with('dataarr',$dataarr)
            ->with('data',json_encode($dataarr,JSON_UNESCAPED_UNICODE));
    }

    /**
     * @param Request $request
     * @return \Illuminate\Contracts\View\Factory|\Illuminate\View\View
     */
    public function checkchart(Request $request){
        $area_first = array();
        $area_first = Area::where('name','青岛市')->first()->toArray();
        $parent_path = $area_first['node'].','.$area_first['id'];
        $where = array();
        $where[] = array('node', 'like', $parent_path.'%');
        $area = Area::where($where)->select(array('id','pid','node','name','level'))->get()->toArray();
        $tree = $this->getProvinceTree($area_first, $area);
        $cityname = '崂山区';
        return view('admin/work/checkchart')
            ->with('tree',$tree)
            ->with('cityname',$cityname);
    }

    /**
     * @param Request $request
     * @return mixed
     */
    public function checkchartcontent(Request $request){
        $area_first = array();
        $area_first = Area::where('name','青岛市')->first()->toArray();
        $parent_path = $area_first['node'].','.$area_first['id'];
        $where = array();
        $where[] = array('node', 'like', $parent_path.'%');
        $area = Area::where($where)->select(array('id','pid','node','name','level'))->get()->toArray();
        $tree = $this->getProvinceTree($area_first, $area);
        $areaid = [];
        if (!empty($request->input('year'))) {
            $year = $request->input('year');
        }else{
            $year = date('Y',time());
        }
        if (!empty($request->input('city'))){
            $pid = $request->input('city');
            $level = $request->input('level');
            switch ($level) {
                case 1:
                    $diqu = 'district';
                    break;
                case 2:
                    $diqu = 'street';
                    break;
                case 3:
                    $diqu = 'community';
                    break;
            }
        }else{
            $pid = 370212;
            $diqu = 'street';
            $level = 2;
        }
        if ($level == 4){
            $area = DB::table('area')->where('id','=',$pid)->select(['id','name'])->get();
            $diqu = 'community';
        }else{
            $area = DB::table('area')->where('pid','=',$pid)->select(['id','name'])->get();
        }
        $cityname = Area::find($pid)->name;
        foreach ($area as $item){
            array_push($areaid,$item->id);
        }
        $data  = Company::leftJoin('area','company.'.$diqu,'area.id')->leftJoin('comcheck','company.id','comcheck.companyId')->select('company.id','area.name','comcheck.wordsUrl','comcheck.id as checkId')->whereIn('company.'.$diqu,$areaid)->where('comcheck.created','like','%'.$year.'%')->orderBy('company.id','desc')->get();
        $data = json_decode(json_encode($data, true));
        $comcheckId = [];
        foreach ($data as $v){
            !empty($v->checkId)?array_push($comcheckId,$v->checkId):'';
        }
        $comcheckdetails  = Comcheckdetail::select('id','checkId','status')->whereIn('checkId',$comcheckId)->orderBy('id','desc')->get();
        $comcheckdetailsdata = json_decode(json_encode($comcheckdetails, true));

        $comcheckagain  = Comcheckagain::select('id','checkId')->whereIn('checkId',$comcheckId)->orderBy('id','desc')->get();
        $comcheckagaindata = json_decode(json_encode($comcheckagain, true));

        $dataarr = [];
        foreach ($area as $item){
            $arr = [];
            $num = 0;
            $checknum = 0;
            $yinhuannum = 0;
            $againnum = 0;
            foreach ($data as $da){
                if ($da->name == $item->name){
                    $num += 1;
                    if (!empty($da->wordsUrl)){
                        $checknum += 1;
                    }
                    foreach ($comcheckdetailsdata as $value){
                        if ($value->checkId == $da->checkId &&$value->status == 2){
                            $yinhuannum += 1;
                        }
                    }
                    foreach ($comcheckagaindata as $value){
                        if ($value->checkId == $da->checkId){
                            $againnum += 1;
                        }
                    }
                }
            }
            $arr = ['product'=>$item->name,'企业监管数量'=>$num,'实际检查数量'=>$checknum,'检查存在隐患数量'=>$yinhuannum,'复查记录数量'=>$againnum];
            array_push($dataarr,$arr);
        }
        //事故隐患分析
        $status = [
            [
                'status' => 1,
                'title'=>'人的不安全行为'
            ],
            [
                'status' => 2,
                'title'=>'物的不安全状态'
            ],
            [
                'status' => 3,
                'title'=>'作业环境的缺陷'
            ],
            [
                'status' => 4,
                'title'=>'安全管理的缺陷'
            ],
        ];
        $data1  = Company::leftJoin('area','company.'.$diqu,'area.id')->leftJoin('comcheck','company.id','comcheck.companyId')->select('company.id','area.name','comcheck.wordsUrl','comcheck.id as checkId')->whereIn('company.'.$diqu,$areaid)->orderBy('company.id','desc')->get();
        $data1 = json_decode(json_encode($data1, true));
        $comcheckId = [];
        foreach ($data1 as $v){
            !empty($v->checkId)?array_push($comcheckId,$v->checkId):'';
        }
        $comcheckdetails  = Comcheckdetail::leftJoin('checkgroup','comcheckdetail.groupId','checkgroup.id')->select('comcheckdetail.id','checkgroup.status as groupstatus','checkgroup.type')->whereIn('comcheckdetail.checkId',$comcheckId)->where(['comcheckdetail.status'=>2])->orderBy('comcheckdetail.id','desc')->get();
        $comcheckdetailsdata = json_decode(json_encode($comcheckdetails, true));

        $comcheckagain  = Comcheckagain::select('id','checkId')->whereIn('checkId',$comcheckId)->orderBy('id','desc')->get();
        $comcheckagaindata = json_decode(json_encode($comcheckagain, true));
        $againId = [];
        foreach ($comcheckagain as $item){
            array_push($againId,$item->id);
        }

        $comcheckagaindetails  = Comcheckagaindetail::leftJoin('checkgroup','comcheckagaindetail.groupId','checkgroup.id')->select('comcheckagaindetail.id','comcheckagaindetail.status','checkgroup.type')->whereIn('comcheckagaindetail.checkId',$againId)->orderBy('comcheckagaindetail.id','desc')->get();
        $comcheckagaindetailsdata = json_decode(json_encode($comcheckagaindetails, true));
        //事故隐患统计图
        $dataarr1 = [];
        foreach ($status as $item){
            $arr = [];
            $xiaofang = 0;
            $shengchan = 0;
            foreach ($comcheckdetailsdata as $value){
                if ($value->groupstatus == $item['status']){
                    if ($value->type == 1){
                        $xiaofang += 1;
                    }
                    if ($value->type == 2){
                        $shengchan += 1;
                    }
                }
            }
            $arr = ['product'=>$item['title'],'消防安全隐患'=>$xiaofang,'生产安全隐患'=>$shengchan];
            array_push($dataarr1,$arr);
        }
        //隐患整改情况统计图
        $all = 0;
        $allxf = 0;
        $allsc = 0;
        $yizhenggai = 0;
        $yizhenggaixf = 0;
        $yizhenggaisc = 0;
        $bufenzhenggai = 0;
        $bufenzhenggaixf = 0;
        $bufenzhenggaisc = 0;
        foreach ($comcheckagaindetailsdata as $value){
            if ($value->type == 1){
                if ($value->status == 2){
                    $bufenzhenggai += 1;
                    $bufenzhenggaixf += 1;
                }
                if ($value->status == 3){
                    $yizhenggai += 1;
                    $yizhenggaixf += 1;
                }
            }
            if ($value->type == 2){
                if ($value->status == 2){
                    $bufenzhenggai += 1;
                    $bufenzhenggaisc += 1;
                }
                if ($value->status == 3){
                    $yizhenggai += 1;
                    $yizhenggaisc += 1;
                }
            }
        }
        foreach ($dataarr1 as $v){
            $all += $v['消防安全隐患'];
            $allxf += $v['消防安全隐患'];
            $all += $v['生产安全隐患'];
            $allsc += $v['生产安全隐患'];
        }
        $zhenggai = [
            ['value'=>$yizhenggai,'name'=>'已整改','itemStyle'=>['color'=>'#1acd7e']],
            ['value'=>$bufenzhenggai,'name'=>'部分整改'],
            ['value'=>($all-$yizhenggai-$bufenzhenggai),'name'=>'未整改'],
        ];
        $zhenggaixf = [
            ['value'=>$yizhenggaixf,'name'=>'已整改','itemStyle'=>['color'=>'#1acd7e']],
            ['value'=>$bufenzhenggaixf,'name'=>'部分整改'],
            ['value'=>($allxf-$yizhenggaixf-$bufenzhenggaixf),'name'=>'未整改'],
        ];
        $zhenggaisc = [
            ['value'=>$yizhenggaisc,'name'=>'已整改','itemStyle'=>['color'=>'#1acd7e']],
            ['value'=>$bufenzhenggaisc,'name'=>'部分整改',],
            ['value'=>($allsc-$yizhenggaisc-$bufenzhenggaisc),'name'=>'未整改'],
        ];
        return view('admin/work/checkchartcontent')
            ->with('dataarr1',$dataarr1)
            ->with('diqu',$diqu)
            ->with('zhenggai',json_encode($zhenggai,JSON_UNESCAPED_UNICODE))
            ->with('zhenggaixf',json_encode($zhenggaixf,JSON_UNESCAPED_UNICODE))
            ->with('zhenggaisc',json_encode($zhenggaisc,JSON_UNESCAPED_UNICODE))
            ->with('data1',json_encode($dataarr1,JSON_UNESCAPED_UNICODE))
            ->with('dataarr',$dataarr)
            ->with('tree',$tree)
            ->with('cityname',$cityname)
            ->with('data',json_encode($dataarr,JSON_UNESCAPED_UNICODE));
    }

    /**
     * @return mixed
     */
    public function firstcheckchart(){
        $areaid = [];
        $status = [
            [
                'status' => 1,
                'title'=>'人的不安全行为'
            ],
            [
                'status' => 2,
                'title'=>'物的不安全状态'
            ],
            [
                'status' => 3,
                'title'=>'作业环境的缺陷'
            ],
            [
                'status' => 4,
                'title'=>'安全管理的缺陷'
            ],
        ];
        $area = DB::table('area')->where('pid','=','370200')->select(['id','name'])->get();
        $diqu = Area::find(370200)->name;
        foreach ($area as $item){
            array_push($areaid,$item->id);
        }
        $data  = Company::leftJoin('area','company.district','area.id')->leftJoin('comcheck','company.id','comcheck.companyId')->select('company.id','area.name','comcheck.wordsUrl','comcheck.id as checkId')->whereIn('company.district',$areaid)->orderBy('company.id','desc')->get();
        $data = json_decode(json_encode($data, true));
        $comcheckId = [];
        foreach ($data as $v){
            !empty($v->checkId)?array_push($comcheckId,$v->checkId):'';
        }
        $comcheckdetails  = Comcheckdetail::leftJoin('checkgroup','comcheckdetail.groupId','checkgroup.id')->select('comcheckdetail.id','checkgroup.status as groupstatus','checkgroup.type')->whereIn('comcheckdetail.checkId',$comcheckId)->where(['comcheckdetail.status'=>2])->orderBy('comcheckdetail.id','desc')->get();
        $comcheckdetailsdata = json_decode(json_encode($comcheckdetails, true));

        $comcheckagain  = Comcheckagain::select('id','checkId')->whereIn('checkId',$comcheckId)->orderBy('id','desc')->get();
        $comcheckagaindata = json_decode(json_encode($comcheckagain, true));
        $againId = [];
        foreach ($comcheckagain as $item){
            array_push($againId,$item->id);
        }

        $comcheckagaindetails  = Comcheckagaindetail::leftJoin('checkgroup','comcheckagaindetail.groupId','checkgroup.id')->select('comcheckagaindetail.id','comcheckagaindetail.status','checkgroup.type')->whereIn('comcheckagaindetail.checkId',$againId)->where(['comcheckagaindetail.status'=>2])->orderBy('comcheckagaindetail.id','desc')->get();
        $comcheckagaindetailsdata = json_decode(json_encode($comcheckagaindetails, true));
        //事故隐患统计图
        $dataarr = [];
        foreach ($status as $item){
            $arr = [];
            $xiaofang = 0;
            $shengchan = 0;
            foreach ($comcheckdetailsdata as $value){
                if ($value->groupstatus == $item['status']){
                    if ($value->type == 1){
                        $xiaofang += 1;
                    }
                    if ($value->type == 2){
                        $shengchan += 1;
                    }
                }
            }
            $arr = ['product'=>$item['title'],'消防安全隐患'=>$xiaofang,'生产安全隐患'=>$shengchan];
            array_push($dataarr,$arr);
        }
        //隐患整改情况统计图
        $all = 0;
        $allxf = 0;
        $allsc = 0;
        $yizhenggai = 0;
        $yizhenggaixf = 0;
        $yizhenggaisc = 0;
        $bufenzhenggai = 0;
        $bufenzhenggaixf = 0;
        $bufenzhenggaisc = 0;
        foreach ($comcheckagaindetailsdata as $value){
            if ($value->type == 1){
                if ($value->status == 2){
                    $bufenzhenggai += 1;
                    $bufenzhenggaixf += 1;
                }
                if ($value->status == 3){
                    $yizhenggai += 1;
                    $yizhenggaixf += 1;
                }
            }
            if ($value->type == 2){
                if ($value->status == 2){
                    $bufenzhenggai += 1;
                    $bufenzhenggaisc += 1;
                }
                if ($value->status == 3){
                    $yizhenggai += 1;
                    $yizhenggaisc += 1;
                }
            }
        }
        foreach ($dataarr as $v){
            $all += $v['消防安全隐患'];
            $allxf += $v['消防安全隐患'];
            $all += $v['生产安全隐患'];
            $allsc += $v['生产安全隐患'];
        }
        $zhenggai = [
            ['value'=>$yizhenggai,'name'=>'已整改','itemStyle'=>['color'=>'#1acd7e']],
            ['value'=>$bufenzhenggai,'name'=>'部分整改'],
            ['value'=>($all-$yizhenggai-$bufenzhenggai),'name'=>'未整改'],
        ];
        $zhenggaixf = [
            ['value'=>$yizhenggaixf,'name'=>'已整改','itemStyle'=>['color'=>'#1acd7e']],
            ['value'=>$bufenzhenggaixf,'name'=>'部分整改'],
            ['value'=>($allxf-$yizhenggaixf-$bufenzhenggaixf),'name'=>'未整改'],
        ];
        $zhenggaisc = [
            ['value'=>$yizhenggaisc,'name'=>'已整改','itemStyle'=>['color'=>'#1acd7e']],
            ['value'=>$bufenzhenggaisc,'name'=>'部分整改',],
            ['value'=>($allsc-$yizhenggaisc-$bufenzhenggaisc),'name'=>'未整改'],
        ];
        return view('admin/work/firstcheckchart')
            ->with('dataarr',$dataarr)
            ->with('diqu',$diqu)
            ->with('zhenggai',json_encode($zhenggai,JSON_UNESCAPED_UNICODE))
            ->with('zhenggaixf',json_encode($zhenggaixf,JSON_UNESCAPED_UNICODE))
            ->with('zhenggaisc',json_encode($zhenggaisc,JSON_UNESCAPED_UNICODE))
            ->with('data',json_encode($dataarr,JSON_UNESCAPED_UNICODE));
    }

    public function test(){
        return view('admin/work/test');

    }

    /**
     * @param $area_first
     * @param $area
     * @return mixed
     */
    protected function getProvinceTree($area_first,$area)
    {
        $area_tree = $area_first;
        $district_street = array();
        $community = array();
        foreach($area as $k=>$v) {
            if($v['level']!=5) {
                $district_street[] = $v;
            } else {
                $community[] = $v;
            }
        }
        $district_street = array_column($district_street, null, 'id');
        $community_arr = array();
        foreach ($community as $key => $value) {
            $community_arr[$value['pid']][] = $value;
        }
        /*获取市的各区*/
        foreach($district_street as $k=>$v) {
            if($v['level']==3 && $v['name'] == '崂山区') {
                $area_tree['district'][] = $v;
            }
        }
        /*获取街道*/
        foreach ($area_tree['district'] as $key => $value) {
            foreach($district_street as $k=>$v) {
                if($value['id']==$v['pid']) {
                    $area_tree['district'][$key]['street'][] = $v;
                }
            }
        }
        /*获取社区*/
        foreach($area_tree['district'] as $k=>$v) {
            foreach($v['street'] as $key=>$value) {
                if(isset($community_arr[$value['id']])) {
                    $area_tree['district'][$k]['street'][$key]['community'] = $community_arr[$value['id']];
                } else {
                    $area_tree['district'][$k]['street'][$key]['community'] = array();
                }
            }
        }
        return $area_tree;
    }

    /**
     * @param Request $quest
     * @return \Illuminate\Contracts\View\Factory|\Illuminate\View\View
     */
    public function checktimes(Request $quest)
    {
        $area_first = array();
        $area_first = Area::where('name', '青岛市')->first()->toArray();
        $parent_path = $area_first['node'] . ',' . $area_first['id'];
        $where = array();
        $where[] = array('node', 'like', $parent_path . '%');
        $area = Area::where($where)->select(array('id', 'pid', 'node', 'name', 'level'))->get()->toArray();
        $tree = $this->getProvinceTree($area_first, $area);
        return view('admin/work/checktimes')
            ->with('tree',$tree);
    }

    /**
     * @param Request $quest
     * @return mixed
     */
    public function checktimescontent(Request $quest)
    {
        $area_first = array();
        $area_first = Area::where('name','青岛市')->first()->toArray();
        $parent_path = $area_first['node'].','.$area_first['id'];
        $where = array();
        $where[] = array('node', 'like', $parent_path.'%');
        $area = Area::where($where)->select(array('id','pid','node','name','level'))->get()->toArray();
        $tree = $this->getProvinceTree($area_first, $area);
        $categoryId = $quest->input('categoryId');
        $areaid = [];
        if (!empty($quest->input('year'))) {
            $year = $quest->input('year');
        }else{
            $year = date('Y',time());
        }
        if (!empty($quest->input('city'))){
            $pid = $quest->input('city');
            $level = $quest->input('level');
            switch ($level) {
                case 1:
                    $diqu = 'district';
                    break;
                case 2:
                    $diqu = 'street';
                    break;
                case 3:
                    $diqu = 'community';
                    break;
            }
        }else{
            $pid = 370212;
            $diqu = 'street';
            $level = 2;
        }
        if ($level == 4){
            $area = DB::table('area')->where('id','=',$pid)->select(['id','name'])->get();
        }else{
            $area = DB::table('area')->where('pid','=',$pid)->select(['id','name'])->get();
        }
        foreach ($area as $item){
            array_push($areaid,$item->id);
        }
        if (!empty($categoryId)){
            if ($level == 4){
                $companyCom = Company::leftJoin('comcheck','company.id','comcheck.companyId')->select('company.*','comcheck.id as checkId','comcheck.created')->where(['company.categoryId'=>$categoryId])->where('comcheck.created','like','%'.$year.'%')->whereIn('company.community',$areaid);
            }else{
                $companyCom = Company::leftJoin('comcheck','company.id','comcheck.companyId')->select('company.*','comcheck.id as checkId','comcheck.created')->where(['company.categoryId'=>$categoryId])->where('comcheck.created','like','%'.$year.'%')->whereIn('company.'.$diqu,$areaid);
            }
        }else{
            if ($level == 4){
                $companyCom = Company::leftJoin('comcheck','company.id','comcheck.companyId')->select('company.*','comcheck.id as checkId','comcheck.created')->where('comcheck.created','like','%'.$year.'%')->whereIn('company.community',$areaid);
            }else{
                $companyCom = Company::leftJoin('comcheck','company.id','comcheck.companyId')->select('company.*','comcheck.id as checkId','comcheck.created')->where('comcheck.created','like','%'.$year.'%')->whereIn('company.'.$diqu,$areaid);
            }
        }
        $companys = $companyCom->get();
        $checkId = [];
        foreach($companys as $com){
            array_push($checkId,$com->checkId);
        }
        $comcheckdetail = Comcheckdetail::whereIn('checkId',$checkId)->get();
        $comcheckagian = Comcheckagain::whereIn('checkId',$checkId)->get();
        $categorys = Checkcategory::all();
        return view('admin/work/checktimescontent')
            ->with('companys',$companys)
            ->with('tree',$tree)
            ->with('categorys',$categorys)
            ->with('comcheckagian',$comcheckagian)
            ->with('comcheckdetail',$comcheckdetail);

    }

    /**
     * @param Request $quest
     * @throws \PHPExcel_Exception
     * @throws \PHPExcel_Writer_Exception
     */
    public function checktimesreport(Request $quest){
        $categoryId = $quest->input('categoryId');
        $areaid = [];
        if (!empty($quest->input('year'))) {
            $year = $quest->input('year');
        }else{
            $year = date('Y',time());
        }
        if (!empty($quest->input('city'))){
            $pid = $quest->input('city');
            $level = $quest->input('level');
            switch ($level) {
                case 1:
                    $diqu = 'district';
                    break;
                case 2:
                    $diqu = 'street';
                    break;
                case 3:
                    $diqu = 'community';
                    break;
            }
        }else{
            $pid = 370212;
            $diqu = 'street';
            $level = 2;
        }
        if ($level == 4){
            $area = DB::table('area')->where('id','=',$pid)->select(['id','name'])->get();
        }else{
            $area = DB::table('area')->where('pid','=',$pid)->select(['id','name'])->get();
        }
        foreach ($area as $item){
            array_push($areaid,$item->id);
        }
        if (!empty($categoryId)){
            if ($level == 4){
                $companyCom = Company::leftJoin('comcheck','company.id','comcheck.companyId')->select('company.*','comcheck.id as checkId','comcheck.created')->where(['company.categoryId'=>$categoryId])->where('comcheck.created','like','%'.$year.'%')->whereIn('company.community',$areaid);
            }else{
                $companyCom = Company::leftJoin('comcheck','company.id','comcheck.companyId')->select('company.*','comcheck.id as checkId','comcheck.created')->where(['company.categoryId'=>$categoryId])->where('comcheck.created','like','%'.$year.'%')->whereIn('company.'.$diqu,$areaid);
            }
        }else{
            if ($level == 4){
                $companyCom = Company::leftJoin('comcheck','company.id','comcheck.companyId')->select('company.*','comcheck.id as checkId','comcheck.created')->where('comcheck.created','like','%'.$year.'%')->whereIn('company.community',$areaid);
            }else{
                $companyCom = Company::leftJoin('comcheck','company.id','comcheck.companyId')->select('company.*','comcheck.id as checkId','comcheck.created')->where('comcheck.created','like','%'.$year.'%')->whereIn('company.'.$diqu,$areaid);
            }
        }
        $companys = $companyCom->get();
        $checkId = [];
        foreach($companys as $com){
            array_push($checkId,$com->checkId);
        }
        $comcheckdetail = Comcheckdetail::whereIn('checkId',$checkId)->get();
        $comcheckagian = Comcheckagain::whereIn('checkId',$checkId)->get();
        //开始写入
        $excel = new \PHPExcel();
        $excel->getDefaultStyle()->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
        $objActSheet = $excel->getActiveSheet();
        $letter = array('A','B','C','D','E','F','G','H','I','J','K','L','M');
        $objActSheet->setCellValue("A1","九小场所检查企业档案统计表");
        $objActSheet->mergeCells('A1:M1');
        $objActSheet->getStyle('A1')->getAlignment()->setWrapText(true);
        $objActSheet->getStyle('A1')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);//左右居中
        $objActSheet->getStyle('A1')->getFont()->setName('黑体');
        $objActSheet->getStyle('A1')->getFont()->setSize(20);
        $objActSheet->getStyle('A1')->getFont()->setBold(true);
        //part1
        $objActSheet->setCellValue("A2","序号");
        $objActSheet->setCellValue("B2","企业名称");
        $objActSheet->setCellValue("C2","初查");
        $objActSheet->setCellValue("D2","复查");
        $objActSheet->setCellValue("E2","二次检查");
        $objActSheet->setCellValue("F2","二次复查");
        $objActSheet->setCellValue("G2","三次检查");
        $objActSheet->setCellValue("H2","三次复查");
        $objActSheet->setCellValue("I2","四次检查");
        $objActSheet->setCellValue("J2","四次复查");
        $objActSheet->setCellValue("K2","五次检查");
        $objActSheet->setCellValue("L2","五次复查");
        $objActSheet->setCellValue("M2","总检查次数");
        $orirow = 3;
        $i = 1;
        foreach ($companys as $company){
            $objActSheet->setCellValue("A".$orirow,$i);
            $objActSheet->setCellValue("B".$orirow,$company->comName);
            $set = 0;$setagian = 0;
            foreach($comcheckdetail as $com) {if($com->checkId == $company->checkId){ $set = 1;} }
            foreach($comcheckagian as $again) {if($again->checkId == $company->checkId) {$setagian = 1;}}
            $objActSheet->setCellValue("C".$orirow,$set?1:0);
            $objActSheet->setCellValue("D".$orirow,$setagian?1:0);
            $objActSheet->setCellValue("E".$orirow,0);
            $objActSheet->setCellValue("F".$orirow,0);
            $objActSheet->setCellValue("G".$orirow,0);
            $objActSheet->setCellValue("H".$orirow,0);
            $objActSheet->setCellValue("I".$orirow,0);
            $objActSheet->setCellValue("J".$orirow,0);
            $objActSheet->setCellValue("K".$orirow,0);
            $objActSheet->setCellValue("L".$orirow,0);
            $objActSheet->setCellValue("M".$orirow,$setagian+$set);
            $orirow++;
            $i++;
        }
        //统一设置填充内容左右居中、字号、宽度
        foreach ($letter as $l){
            for ($x = 2;$x<=$orirow;$x++){
                $objActSheet->getStyle($l.$x)->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);//左右居中
                $objActSheet->getStyle($l.$x)->getFont()->setSize(10);
            }

        }
        for ($x=1; $x<=($orirow+10); $x++) {
            $objActSheet->getRowDimension($x)->setRowHeight(20);//统一设置行高度

        }
        $styleArray = array(
            'borders' => array(
                'allborders' => array(
                    'style' => \PHPExcel_Style_Border::BORDER_THIN,//细边框
                ),
            ),
        );
        $objActSheet->getColumnDimension('B')->setWidth(30);

        $objActSheet->getStyle('A1:M'.($orirow+10))->applyFromArray($styleArray);//设置粗边框
        $objActSheet->getRowDimension(1)->setRowHeight(30);//单独设置行高度
        $write = new \PHPExcel_Writer_Excel5($excel);
        ob_end_clean();
        header("Pragma: public");
        header("Expires: 0");
        header("Cache-Control:must-revalidate, post-check=0, pre-check=0");
        header("Content-Type:application/force-download");
        header("Content-Type:application/vnd.ms-execl");
        header("Content-Type:application/octet-stream");
        header("Content-Type:application/download");;
        header('Content-Disposition:attachment;filename="'.''."九小场所企业检查统计".'.xls"');
        header("Content-Transfer-Encoding:binary");
        $write->save('php://output');
        exit();
    }

    public function detailexport(Request $quest){
//        echo phpinfo();exit();
//        $checkId = $quest->input('checkId');
        $companyId = $quest->input('id');
//        $companyId = 19;
        $comName = Company::find($companyId)->comName;
        $checkId = Comcheck::where(['companyId'=>$companyId])->get()[0]->id;
        $data = Comcheckdetail::where(['checkId'=>$checkId])->get();
        $negatives = [];
        $groupId = [];
        $entryId = [];
        foreach ($data as $v){
            $arr = explode(",",$v->negatives);
            foreach ($arr as $v1){
                empty($v1)?'':array_push($negatives,$v1);
            }
            array_push($groupId,$v->groupId);
            array_push($entryId,$v->entryId);
        }
        $negatives = Checknegative::whereIn('id',$negatives)->get();
        $negativesall = Checknegative::whereIn('entryId',$entryId)->get();
//        $groupInfo = Checkgroup::find($groupId);
//        if(empty($groupInfo)){
//            return back();
//        }
//        $comcheckgroup = [];
//        $comcheckgroup = Comcheckgroup::where(['checkId'=>$checkId,'groupId'=>$groupId])->first();
        $entrys = Checkentry::whereIn('groupId',$groupId)->get();
        //开始写入
        $excel = new \PHPExcel();
        $excel->getDefaultStyle()->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
        $objActSheet = $excel->getActiveSheet();
        $letter = array('A','B','C','D','E');
        $objActSheet->setCellValue("A1",$comName."现场检查记录表");
        $objActSheet->mergeCells('A1:E1');
        $objActSheet->getStyle('A1')->getAlignment()->setWrapText(true);
        $objActSheet->getStyle('A1')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);//左右居中
        $objActSheet->getStyle('A1')->getFont()->setName('黑体');
        $objActSheet->getStyle('A1')->getFont()->setSize(20);
        $objActSheet->getStyle('A1')->getFont()->setBold(true);
        //part1
        $objActSheet->setCellValue("A2","序号");
        $objActSheet->setCellValue("B2","检查内容");
        $objActSheet->setCellValue("C2","检查方法");
        $objActSheet->setCellValue("D2","检查结果");
        $objActSheet->setCellValue("E2","取证资料");
        $orirow = 3;
        $i=1;
        for ($x=1; $x<=(1000); $x++) {
            $objActSheet->getRowDimension($x)->setRowHeight(20);//统一设置行高度

        }
        foreach ($entrys as $entry){
            foreach ($data as $da){
                if($da->entryId == $entry->id){
                    $objActSheet->setCellValue("A".$orirow,$i);
                    $objActSheet->setCellValue("B".$orirow,$entry->entry);
                    $i++;
                    if (!empty($da->pictures)){
                        $pic = explode(',',$da->pictures);
                        foreach ($pic as $ve){
                            if (!empty($ve)){
                                $img=new \PHPExcel_Worksheet_Drawing();
                                $img->setPath(str_replace('\\', '/', base_path()).'/public'.$ve);//写入图片路径
                                $img->setHeight(300);//写入图片高度
                                $img->setWidth(300);//写入图片宽度
                                $objActSheet->getRowDimension($orirow)->setRowHeight(130);//统一设置行高度

                                $img->setOffsetX(1);//写入图片在指定格中的X坐标值
                                $img->setOffsetY(1);//写入图片在指定格中的Y坐标值
                                $img->setRotation(1);//设置旋转角度
                                $img->getShadow()->setVisible(true);//
                                $img->getShadow()->setDirection(50);//
                                $img->setCoordinates("E".$orirow);//设置图片所在表格位置
                                $img->setWorksheet($excel->getActiveSheet());//把图片写到当前的表格中
                            }
                        }
                    }
                    foreach ($negativesall as $ne){
                        if ($da->entryId == $ne->entryId){
                            $objActSheet->setCellValue("C".$orirow,$ne->content);
                            $objActSheet->setCellValue("D".$orirow,'合格');
                            $arr = explode(',',$da->negatives);
                            foreach ($arr as $ar) {
                                if ($ne->id == $ar && !empty($ne)){
                                    $objActSheet->setCellValue("D".$orirow,'不合格');
                                    $phpColor = new \PHPExcel_Style_Color();
                                    $phpColor->setRGB('FF0000');
                                    $excel->getActiveSheet()->getStyle("D".$orirow)->getFont()->setColor( $phpColor );

                                }
                            }
                            $orirow++;
                        }
                    }
                }
            }
        }
        $orirow++;

        //统一设置填充内容左右居中、字号、宽度
        foreach ($letter as $l){
            for ($x = 2;$x<=$orirow;$x++){
                $objActSheet->getStyle($l.$x)->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);//左右居中
                $objActSheet->getStyle($l.$x)->getFont()->setSize(10);
            }

        }

        $styleArray = array(
            'borders' => array(
                'allborders' => array(
                    'style' => \PHPExcel_Style_Border::BORDER_THIN,//细边框
                ),
            ),
        );
        $objActSheet->getColumnDimension('A')->setWidth(10);
        $objActSheet->getColumnDimension('B')->setWidth(90);
        $objActSheet->getColumnDimension('C')->setWidth(60);
        $objActSheet->getColumnDimension('D')->setWidth(20);
        $objActSheet->getColumnDimension('E')->setWidth(50);

        $objActSheet->getStyle('A1:E'.($orirow+10))->applyFromArray($styleArray);//设置粗边框
        $objActSheet->getRowDimension(1)->setRowHeight(30);//单独设置行高度
        $write = new \PHPExcel_Writer_Excel5($excel);
        ob_end_clean();
        header("Pragma: public");
        header("Expires: 0");
        header("Cache-Control:must-revalidate, post-check=0, pre-check=0");
        header("Content-Type:application/force-download");
        header("Content-Type:application/vnd.ms-execl");
        header("Content-Type:application/octet-stream");
        header("Content-Type:application/download");;
        header('Content-Disposition:attachment;filename="'.$comName.''."现场检查记录表".'.xls"');
        header("Content-Transfer-Encoding:binary");
        $write->save('php://output');
        exit();
    }

    /**
     * 获取当前用户信息
     * @param Request $quest
     * @return \Illuminate\Contracts\View\Factory|\Illuminate\View\View
     */
    public function getUser(Request $quest)
    {
        $user=Auth::guard('admin')->user();
        $res_arr['data'] = $user;
        $res_arr["code"] = 200;
        $res_arr["message"] = 'success';
        return response()->json($res_arr);
    }
}
